Need an Excell function that can lookup vertical AND horizontal

  • Thread starter Thread starter Piet vd Berg
  • Start date Start date
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
 
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
 
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))
 
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
Click to expand...


http://www.contextures.com/xlFunctions03.html#IndexMatch2
 
Back
Top