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

I'm confused. Wouldn't the corresponding row number in column A in the range from A5:A21be 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

Thank you. I wanted the relative row number and it worked.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History