Index, Match within a range of values

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
 
D

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!
 
J

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

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