ExcelBanter - lookup problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - lookup problem (https://www.excelbanter.com/excel-discussion-misc-queries/123730-lookup-problem.html)

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets say
the value is "C11524-1". How do I search for this value in the current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay

=INDEX(A1:A5,MATCH("*C11524-1*",B1:B5,0))

" wrote:

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets say
the value is "C11524-1". How do I search for this value in the current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay

Thanks for the suggestion but in this case the value that I was looking
for is in the cell E3 and therefore I want to get the value in cell A3
as a result. The problem is that I don't know which column or row the
value I am looking for is in. The value is somewhere in the table
B1:E5.

Teethless mama wrote:
=INDEX(A1:A5,MATCH("*C11524-1*",B1:B5,0))

" wrote:

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets say
the value is "C11524-1". How do I search for this value in the current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay

Try this:

A10 = lookup value

I'm assuming your table is in the range A1:E5.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A1:A5,MAX((B1:E5=A10)*(ROW(B1:E5))),0)

Biff

wrote in message
ups.com...
Thanks for the suggestion but in this case the value that I was looking
for is in the cell E3 and therefore I want to get the value in cell A3
as a result. The problem is that I don't know which column or row the
value I am looking for is in. The value is somewhere in the table
B1:E5.

Teethless mama wrote:
=INDEX(A1:A5,MATCH("*C11524-1*",B1:B5,0))

" wrote:

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets say
the value is "C11524-1". How do I search for this value in the current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay

Thank you very much... This is what I was looking for...

Tuncay

T. Valko wrote:
Try this:

A10 = lookup value

I'm assuming your table is in the range A1:E5.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A1:A5,MAX((B1:E5=A10)*(ROW(B1:E5))),0)

Biff

wrote in message
ups.com...
Thanks for the suggestion but in this case the value that I was looking
for is in the cell E3 and therefore I want to get the value in cell A3
as a result. The problem is that I don't know which column or row the
value I am looking for is in. The value is somewhere in the table
B1:E5.

Teethless mama wrote:
=INDEX(A1:A5,MATCH("*C11524-1*",B1:B5,0))

" wrote:

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets say
the value is "C11524-1". How do I search for this value in the current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay

You're welcome. Thanks for the feedback!

Just in case your table does not start on row 1 (A1:E5), then you have to
account for the offset. Say the table was in the range A7:E11 with the
lookup value in cell A1:

Still array entered (CTRL,SHIFT,ENTER):

=INDEX(A7:A11,MAX((B7:E11=A1)*(ROW(B7:E11)-MIN(ROW(B7:E11))+1)),0)

Or:

=INDEX(A7:A11,MAX((B7:E11=A1)*(ROW(B7:E11)-ROW(B7)+1)),0)

Biff

wrote in message
oups.com...
Thank you very much... This is what I was looking for...

Tuncay

T. Valko wrote:
Try this:

A10 = lookup value

I'm assuming your table is in the range A1:E5.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A1:A5,MAX((B1:E5=A10)*(ROW(B1:E5))),0)

Biff

wrote in message
ups.com...
Thanks for the suggestion but in this case the value that I was looking
for is in the cell E3 and therefore I want to get the value in cell A3
as a result. The problem is that I don't know which column or row the
value I am looking for is in. The value is somewhere in the table
B1:E5.

Teethless mama wrote:
=INDEX(A1:A5,MATCH("*C11524-1*",B1:B5,0))

" wrote:

I have a table in my worksheet as follows:

'+1V5' C12809-1 C12810-1
'+1V5_FPGA_dec' R12805-1 C12820-1 TP12844-1 C12821-2
'+1V8' TP11847-1 L11804-2 U11800-6 C11524-1
'+1V8' C11533-1 C11820-1 R11010-1 C11824-1
'+1V8_7020_PLL' C11826-1 U11500-N23 U11500-N22 C11801-2

I have the value I want to find stored in another worksheet. Lets
say
the value is "C11524-1". How do I search for this value in the
current
worksheet to get the cell value of the first column in the same row
where the value is found. In the above example I should be able to
get
'+1V8' as a result.

I couldn't figure out a way to do this using vlookup or index/match.

Any help will be greatly appreciated.

Tuncay