Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Table Lookup formula where 2 known values are inside array

I am using Excel.

Please help, I am stuck on a non-typical table lookup
I regularly use =lookup to get values where the relationship is 1:1
e.g. in a cell, I put a value, I use =lookup for that value's meaning

I need to do this, but the lookup is a table, not a 1:1 column match
Row 1 is a header, one of my search values
Column 1 is a value I want to find
the table (array) contains the other known value

I need do do a enter values in 2 cells, and have a lookup return a value
based on the value it looks up.

eg:

-- | 1 | 2 | 3 | 4
w | a | d | h | i
x | b | e | i | j
y | c | f | j | k
z | d | g | k | l

in my lookup, one cell has the value 2, This should specify to use the
column with the value 2 in the first row
The other cell has the value f. The formula I am having a hard time
figuring out needs to return the value in column 1, which in this case is the
letter y.

I have tried vlookup, hlookup, match and so on, but I am just plain stuck.
Please help.


  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Table Lookup formula where 2 known values are inside array

Try this:

With your table in the range A1:E5

A10 = column lookup_value = 2
B10 = table lookup_value = F

=INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0))

Biff

"excel-lookuper" wrote in message
...
I am using Excel.

Please help, I am stuck on a non-typical table lookup
I regularly use =lookup to get values where the relationship is 1:1
e.g. in a cell, I put a value, I use =lookup for that value's meaning

I need to do this, but the lookup is a table, not a 1:1 column match
Row 1 is a header, one of my search values
Column 1 is a value I want to find
the table (array) contains the other known value

I need do do a enter values in 2 cells, and have a lookup return a value
based on the value it looks up.

eg:

-- | 1 | 2 | 3 | 4
w | a | d | h | i
x | b | e | i | j
y | c | f | j | k
z | d | g | k | l

in my lookup, one cell has the value 2, This should specify to use the
column with the value 2 in the first row
The other cell has the value f. The formula I am having a hard time
figuring out needs to return the value in column 1, which in this case is
the
letter y.

I have tried vlookup, hlookup, match and so on, but I am just plain stuck.
Please help.




  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default Table Lookup formula where 2 known values are inside array

Thank you, your method and formula worked perfectly!

"T. Valko" wrote:

Try this:

With your table in the range A1:E5

A10 = column lookup_value = 2
B10 = table lookup_value = F

=INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0))

Biff

"excel-lookuper" wrote in message
...
I am using Excel.

Please help, I am stuck on a non-typical table lookup
I regularly use =lookup to get values where the relationship is 1:1
e.g. in a cell, I put a value, I use =lookup for that value's meaning

I need to do this, but the lookup is a table, not a 1:1 column match
Row 1 is a header, one of my search values
Column 1 is a value I want to find
the table (array) contains the other known value

I need do do a enter values in 2 cells, and have a lookup return a value
based on the value it looks up.

eg:

-- | 1 | 2 | 3 | 4
w | a | d | h | i
x | b | e | i | j
y | c | f | j | k
z | d | g | k | l

in my lookup, one cell has the value 2, This should specify to use the
column with the value 2 in the first row
The other cell has the value f. The formula I am having a hard time
figuring out needs to return the value in column 1, which in this case is
the
letter y.


  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Table Lookup formula where 2 known values are inside array

You're welcome. Thanks for the feedback!

Biff

"excel-lookuper" wrote in message
...
Thank you, your method and formula worked perfectly!

"T. Valko" wrote:

Try this:

With your table in the range A1:E5

A10 = column lookup_value = 2
B10 = table lookup_value = F

=INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0))

Biff

"excel-lookuper" wrote in
message
...
I am using Excel.

Please help, I am stuck on a non-typical table lookup
I regularly use =lookup to get values where the relationship is 1:1
e.g. in a cell, I put a value, I use =lookup for that value's meaning

I need to do this, but the lookup is a table, not a 1:1 column match
Row 1 is a header, one of my search values
Column 1 is a value I want to find
the table (array) contains the other known value

I need do do a enter values in 2 cells, and have a lookup return a
value
based on the value it looks up.

eg:

-- | 1 | 2 | 3 | 4
w | a | d | h | i
x | b | e | i | j
y | c | f | j | k
z | d | g | k | l

in my lookup, one cell has the value 2, This should specify to use the
column with the value 2 in the first row
The other cell has the value f. The formula I am having a hard time
figuring out needs to return the value in column 1, which in this case
is
the
letter y.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Matrix Math using LOOKUP inside Array {} Function ExcelMonkey Excel Worksheet Functions 4 February 16th 07 12:10 AM
vLookup with multiple lookup value in table array [email protected] Excel Worksheet Functions 2 September 26th 06 04:12 PM
lookup a list of values for an array formula Xbrokylnboy Excel Worksheet Functions 0 June 19th 06 08:12 PM
How do I delete a lookup table array name? Lorraine Excel Worksheet Functions 1 January 30th 06 08:35 PM
find maximum of two values in an array with same lookup value Andy M Excel Discussion (Misc queries) 5 May 13th 05 01:31 PM


All times are GMT +1. The time now is 12:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"