Help using VLOOKUP command

[Scott]

I have 144 unique data fields in cells B5:B148. Each of these data fields is
individually located somewhere (not in order) in cells K5:AH148.

What I want to do is in C5, find the value in B5 somewhere in the K5:AH148
range, and return the value in the very next column to the right.

For instance, the matching B5 data could be in cell S57 for example, and I'd
want to display the data in T57 in cell C5.

Any idea as to how to do this?

Thanks.

 

[Jacob Skaria]

In cell B5 enter the data to be searched
C5 enter the below formula

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"

=INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN($K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2)


If this post helps click Yes

 

[Jacob Skaria]

A simpler one..Again please note that this is an array formula

=INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O$20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20),""))+1))

If this post helps click Yes

 

[Sheeloo]

Very nice.

A simpler one..Again please note that this is an array formula

=INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O$20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20),""))+1))

If this post helps click Yes

 

[Scott]

This is returning a #VALUE! error.

Very nice.

 

[Sheeloo]

Did you press CTRL-SHIFT-ENTER together after typing/pasting the formula?

 

[Jacob Skaria]

Dear Scott

If you have used the second formula; the range was wrong..Try the below

=INDIRECT(ADDRESS(MIN(IF($K$5:$AH$145=B5,ROW($K$5:$AH$145),"")),MIN(IF($K$5:$AH$145=B5,COLUMN($K$5:$AH$145),""))+1))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes

 

[Jacob Skaria]

Thanks Sheeloo...

If this post helps click Yes