Need an Excell function that can lookup vertical AND horizontal

P

Piet vd Berg

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
 
J

Jacob Skaria

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
 
T

T. Valko

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))
 
G

Glenn

Piet said:
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/xlFunctions03.html#IndexMatch2
 
P

p45cal

Piet said:
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 an
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

You can very easily get the result using a formula such as:
=Good Bruce
or
=Bruce Good

As a one-off exercise you need first to do this simple operation:

Select the whole table including row and column headers.
In the dropdown menus (xl2003) choose Insert|Name|Create...
In the dialogue box, tick the checkboxes Top Row and Left Column
Click OK.

The formulae should now produce meaningful results
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top