Need an Excell function that can lookup vertical AND horizontal
 

I need a function or combination of functions that can first search say
vertical in the leftmost column and then horizontal on the top line and then
read a value in the table. It must thereforehave two input criterion.
In the example below it must first look up "Bruce" as input criteria 1, then
look up "Good" as input criteria 2 and then return "6" as the output.

Excellent Good Bad Very Bad
John 8 5 3 1
Peter 10 8 4 2
Bruce 7 6 2 1
Eric 9 8 6 3

Answer: Need an Excell function that can lookup vertical AND horizontal
 

You can use the combination of INDEX and MATCH functions to lookup values both vertically and horizontally. Here's how you can do it:

1. Select the cell where you want to display the result.
2. Type the following formula:
   Code:

   ---

   =INDEX(B2:E5,MATCH(H2,A2:A5,0),MATCH(H3,B1:E1,0))

   ---

3. Press Enter.

Let's break down the formula:

- B2:E5 is the range of cells that contains the data table.
- H2 is the cell that contains the first input criteria (e.g. "Bruce").
- A2:A5 is the range of cells that contains the names in the leftmost column of the table.
- H3 is the cell that contains the second input criteria (e.g. "Good").
- B1:E1 is the range of cells that contains the rating categories in the top row of the table.

The MATCH function is used to find the position of the input criteria in the corresponding row or column. The INDEX function then returns the value at the intersection of the row and column.

So, in the example you provided, the formula would be:

This would return the value 6, which is the rating for "Bruce" and "Good" in the table.

Need an Excell function that can lookup vertical AND horizontal
 

You can use VLOOKUP() itself combined with MATCH() to do this..Try the
below..Replace the text strings with a cell reference if needed

=VLOOKUP("Bruce",A1:E5,MATCH("Good",A1:E1,0),0)

If this post helps click Yes
---------------
Jacob Skaria


"Piet vd Berg" wrote:

I need a function or combination of functions that can first search say
vertical in the leftmost column and then horizontal on the top line and then
read a value in the table. It must thereforehave two input criterion.
In the example below it must first look up "Bruce" as input criteria 1, then
look up "Good" as input criteria 2 and then return "6" as the output.

Excellent Good Bad Very Bad
John 8 5 3 1
Peter 10 8 4 2
Bruce 7 6 2 1
Eric 9 8 6 3

Need an Excell function that can lookup vertical AND horizontal
 

Here's another one...

Column headers in the range B1:E1
Row headers in the range A2:A5

Lookup values:
A10 = Bruce
B10 = good

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

--
Biff
Microsoft Excel MVP


"Piet vd Berg" wrote in message
...
I need a function or combination of functions that can first search say
vertical in the leftmost column and then horizontal on the top line and
then
read a value in the table. It must thereforehave two input criterion.
In the example below it must first look up "Bruce" as input criteria 1,
then
look up "Good" as input criteria 2 and then return "6" as the output.

Excellent Good Bad Very Bad
John 8 5 3 1
Peter 10 8 4 2
Bruce 7 6 2 1
Eric 9 8 6 3

Need an Excell function that can lookup vertical AND horizontal
 

Piet vd Berg wrote:
I need a function or combination of functions that can first search say
vertical in the leftmost column and then horizontal on the top line and then
read a value in the table. It must thereforehave two input criterion.
In the example below it must first look up "Bruce" as input criteria 1, then
look up "Good" as input criteria 2 and then return "6" as the output.

Excellent Good Bad Very Bad
John 8 5 3 1
Peter 10 8 4 2
Bruce 7 6 2 1
Eric 9 8 6 3


http://www.contextures.com/xlFunctio...ml#IndexMatch2