Solved How do i use the if function with a vlookup?

August 26, 2014 at 04:25:27
Specs: Windows 7
If A22 equals a number in the range from B5:B21 then I want the corresponding row number in column A in the range from A5:A21 to be displayed in B22

See More: How do i use the if function with a vlookup?

Report •


#1
August 26, 2014 at 05:45:44
✔ Best Answer
I'm confused. Wouldn't the corresponding row number in column A in the range from A5:A21 be the same as the Row number from Column B where the match was found?

I'm also not sure if you are looking for the actual Excel sheet Row number or the relative Row number from the range you are searching, e.g. B5:B21. In other words, if a match is found in B5, do you want a "5" returned (the Excel Row) or a "1" returned (the Relative Row based on the search range)?

Finally, you didn't say what you want returned if the value is not found in B5:B21. In the formulas below, I'm assuming you want a blank cell.

In any case, I don't think you need VLOOKUP. The MATCH function will return Row numbers directly.

If you want the Excel Row number, try this:

=IF(MATCH(A22,$B$5:$B$21,0)>0,MATCH(A22,$B$1:$B$21,0),"")

Note: This assumes that there is no matching value in B1:B4.

If you want the relative Row number from the range, try this:

=IF(MATCH(A22,$B$5:$B$21,0)>0,MATCH(A22,$B$5:$B$21,0),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
August 27, 2014 at 04:40:25
Thank you. I wanted the relative row number and it worked.

Report •

Related Solutions


Ask Question