Forum Discussion
vlookup formula problem
Hi everyone,
I have a question about the vlookup formula (I guess).
I would like to link two formulas (like IF and VLOOKUP, or IFNA and VLOOKUP, I'm not sure).
Here's my needs: in column B I have a list of names, in column F I have some numbers. But not every name has a number in F column. I need the formula to display me a YES if there's a number, otherwise a NO.
The argument I'm trying to create is:
if VLOOKUP finds a value in B5, and F5 is blank, then return this; if VLOOKUP finds a value in B5, and F5 is NOT blank, then return this; if VLOOKUP does NOT find a value, then return this.
Here's another way:
if VLOOKUP finds a value in B5, and F5 is blank OR if VLOOKUP does NOT find a value, then return this; if VLOOKUP finds a value in B5, and F5 is NOT blank, then return this.
Do you guys have any idea to help me?
Thank you so much
10 Replies
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)
