Index, Match within a range of values

  • Thread starter Thread starter Kara
  • Start date Start date
K

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
 
=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!
 
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))
 
Back
Top