Forum Discussion
vlookup formula problem
I didn't really understand what you're up to.
but here is an example of a "VLOOKUP, IF; AND" combination ,,,,,
= IF (AND (VLOOKUP ($ A3; Sheet2! $ A: $ I; 9; 0)> = (G3 + I3); (N3 <0)); "Order"; IF (AND (G3> = L3; I3> 0); "Cancel"; ""))
maybe you can adapt it to your needs!
otherwise send your table with an example (w/out sensitive data)!
additional infos:
IF function – nested formulas and avoiding pitfalls
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
I'm so sorry, I'm Italian and my English is not so good ahahah...
I attach an example table.
As you see, I have some names in column C, but not all of them have a number in column F.
I need to count how many times there is a name in C with a number in F (there's no way a name can figure once with a number and then without a number, every single name has ALWAYS or NEVER a number)
Here is an example, I created (in another spreadsheet) a list with an example of all the names I need, and these will be the results:
MATT 0
GEORGE 0
KEVIN 2
LUCAS 1
ANTHONY 0
MICHAEL 1
PETER 0
I'm not even sure which formulas I need, I thought about an IF and a VLOOKUP but it can be everything you want...
Thank you so much
As variant that could be
with
=LET( namesRange, C2:C14, numbersRange, F2:F14, uniqueNames, UNIQUE(namesRange), counts, COUNTIFS(numbersRange,">0",namesRange,uniqueNames), rawResult, IF({1,0},uniqueNames,counts), Result,FILTER(rawResult,uniqueNames<>0), Result)If I understand correctly the picture of you and the translation, I will send you this file as a solution.
Sarei felice di sapere se potessi aiutare.
Nikolino
So di non sapere niente (Socrate)
* Segna e vota gentilmente questa risposta se aiuta, per favore, poiché sarà utile per più membri della Comunità che leggono qui.
Thank you very much for interesting in my problem, but unfortunately it's not what I need (it's not your fault obviously)...
I'll try to explain WHY I need all this.
I have a school of motorbike, and it counts like 800 students. Everyone of these 800 can come whenever they want, once, twice or even more, but everyday of lesson, we can accept only the first 100 who subscribe. I have a list of all the 800 names, but of course not all of them come to EVERY SINGLE day of lessons! So I need to count how many times they came.
How do we know if they came in a specific day? Well of course, we can see his name among the 100 subscribers, but we will count him only IF he has a price next to him (if there is not a price, it means that he subscribed but he didn't come, so we don't need to count him).
I attach an example table with the list of all the names and two days of lessons, and the result I need from this formula.
I saw that you put a formula in column C that gives the sum of all the values of a single name (ex. Kevin has 2 values "50", so next to Kevin I will see 50+50=100, Lucas has "25" and "70" so next to Lucas I will always see 25+75=95, and so on).
But I don't need to see the sum of the values. It doesn't matter for me if they have 50, 25, 70 or whatever. I need just to see how many times they have a value!
Maybe the solution from SergeiBaklan fits more my needs, but I'm not so confident with those "xlfn" and "xlpm" formulas... Is there another easier way to solve it?
Thank you so much guys for your effort!!!!
