Index, Match within a range of values

[Kara]

Hi--
I have the following setup. I want to return the value based on what is
entered in A6 and B6. For example, if the value 1 is entered in A6 and the
word Cat is entered in B6, the value 3 should be returned in C6 since 1 is in
between 0 and 2 and I want the value out of the column Cat.

Another example, if the number 8 is entered in A6 and the word Bird in B6,
then the value 8 should be returned.

I am familiar with INDEX and MATCH functions, but do not know how to lookup
between a range of values (between columns A and B).

A B C D E
1 Min # Max # Dog Cat Bird
2 0 2 9 3 5
3 3 6 4 2 0
4 7 9 1 6 8
5
6 1 Cat

Much thanks!
Kara

 

[Dave Peterson]

=index(a1:E4,match(a6,a1:a4,1),match(b6,a1:e1,0))

I'd put that table on a separate worksheet -- save from my typos!

 

[Dave Peterson]

ps. I'd add an extra row that serves as a sanity check:

 

[Jim Thomlinson]

You do not need the range of values. just a closest ascending match...

=INDEX($C$2:$E$4, MATCH(A6, $A$2:$A$4, 1), MATCH(B6, $C$1:$E$1, 0))

 

[Kara]

Thanks both Dave and Jim. Both ways worked perfectly!

ps. I'd add an extra row that serves as a sanity check: