| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup problem
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup problem
=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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup problem
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup problem
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup problem
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup problem
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP problem | Excel Worksheet Functions | |||
| LOOKUP function Problem | Excel Worksheet Functions | |||
| Lookup problem want 2 or more results | Excel Discussion (Misc queries) | |||
| Zeros problem in LOOKUP? | Excel Worksheet Functions | |||
| Lookup Problem | Excel Worksheet Functions | |||
Linear Mode
