Index, Match within a range of values
 

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, Match within a range of values
 

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



Kara wrote:

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, Match within a range of values
 

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

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 10 TooBig TooBig TooBig



Dave Peterson wrote:

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

Kara wrote:

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

--

Dave Peterson

Index, Match within a range of values
 

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

--
HTH...

Jim Thomlinson


"Kara" wrote:

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, Match within a range of values
 

Thanks both Dave and Jim. Both ways worked perfectly!

"Dave Peterson" wrote:

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

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 10 TooBig TooBig TooBig



Dave Peterson wrote:

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

Kara wrote:

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

--

Dave Peterson
.