Index,Matchup Formula Question

J

JDT

Hi,

I have the following formula in a cell:

=INDEX(look!$B$1:$B$22,MATCH(B3,look!$A$1:$A$22))

I created a table of names and numbers on a separate page called
"look."


Here is an example:

Tom 23
Jerry 30
Ed 41
Steve 16
Bob 11

All I want to do on the main page is when I input a name into cell B3
I get the corresponding value on the look page. So, if I put Bob in
cell B3, then in cell B4 it should say 11.

I did it this way because the list is very long.

I got the above formula from another spreadsheet of mine doing
basically the same thing.

Anyway it doesn't work, and I can't find my error. (Not that I knew
what I was doing in the first place.)

Any help appreciated.
 
P

P Sitaram

Try:

=INDEX(look!$B$1:$B$22,MATCH(B3,look!$A$1:$A$22,0))

I added a ",0" in the Match part
 
J

JDT

Try:

=INDEX(look!$B$1:$B$22,MATCH(B3,look!$A$1:$A$22,0))

I added a ",0" in the Match part
Click to expand...

Thanks. This works, but why?

One more question. Can I have a default answer? In other words,
let's say there is no value to look up, can I have it insert a number
I wish, such as 3.6?

Thanks again.
 
D

Dave Peterson

You mean if B3 is empty?

=if(b3="",3.6,INDEX(look!$B$1:$B$22,MATCH(B3,look!$A$1:$A$22,0)))
 
Top