Creating a lookup function within a lookup table

[astewart28]

Ok I'm trying to get a lookup function within another sheet & I'm completely lost hoping someone can direct me..

current formula reads: =IF(ISNUMBER(VLOOKUP(A2,Expenses!$A$2:$E$21,5,100)),"YES","NO")
where if the amount in E2:E21 has an output of 100, the answer would be yes. The entire table of course is A2:E21 but it has more columns than the standard 2, if that makes sense

Not sure if my column # is wrong or ISNUMBER is wrong, my textbook doesn't even have that definition in it, I've had to google so much this course. Thanks in advance!!

 

[astewart28]

FYI: I'm getting an "Yes" for every cell .. where 4 cells should be "No"

 

[Eric W]

Welcome to the forum.

You're including the value you're looking for as a parameter within the VLOOKUP function. What you want to do is check the result of the VLOOKUP with 100, something like:

=IFERROR(IF(VLOOKUP(A2,Expenses!$A$2:$E$21,5)=100,"YES","NO"),"NO")

VLOOKUP will find the value of A2 in Expenses!$A$2:$A$21, and return the value from the same row in Expenses!$E$2:$E$21. Then you compare that with 100, and return "YES" or "NO". If VLOOKUP doesn't find A2, then it returns an error, and the IFERROR will trap that and return "NO". This is probably a simpler way to code it than with your ISNUMBER.

Also note that the parameter in VLOOKUP is pretty important. It's a True/False parameter, where 0=False, and non-zero=True. So when you put 100 there, it assumed True. True means approximate match, False means exact match. So if your range is not sorted, always use False (0). If it's sorted {1,5,7} but you don't want it to match if there's no match {2}, then use False. But if you want an approximate match, say if people get a bonus of 50 for sales between 1000 and 2000, and someone had sales of 1500, use True. You might want to do this:

=IFERROR(IF(VLOOKUP(A2,Expenses!$A$2:$E$21,5,0)=100,"YES","NO"),"NO")

Hope this helps.

 

[astewart28]

Oh my gosh, that would have took me hours of research & inputting data over & over to get that answer. I appreciate it!! Thank you do much!!!!

 

[Eric W]

You're welcome!