Index,Matchup Formula Question
 

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.

Hi

try
=VLOOKUP(B3,look!$A$1:$A$22,2,0)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JDT" wrote in message
news:1113571773.b99a5f7d011c1e84ba38bc138eac67a5@t eranews...
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.

Try:

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

I added a ",0" in the Match part

On 15 Apr 2005 06:47:10 -0700, "P Sitaram"
wrote:

Try:

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

I added a ",0" in the Match part

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.

On Fri, 15 Apr 2005 21:42:33 +0800, "JulieD"
wrote:

Hi

try
=VLOOKUP(B3,look!$A$1:$A$22,2,0)

--
Cheers
JulieD

Hi, thanks, but I couldn't get this to work.

You mean if B3 is empty?

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




JDT wrote:

On 15 Apr 2005 06:47:10 -0700, "P Sitaram"
wrote:

Try:

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

I added a ",0" in the Match part

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.

--

Dave Peterson

I think JulieD meant this:
=VLOOKUP(B3,look!$A$1:$B$22,2,0)

(She's returning the second column and forgot to increase the number of
columns.)

If you want to read more about =vlookup() and =index(match()), take a look at
Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
http://www.contextures.com/xlFunctions03.html

JDT wrote:

On Fri, 15 Apr 2005 21:42:33 +0800, "JulieD"
wrote:

Hi

try
=VLOOKUP(B3,look!$A$1:$A$22,2,0)

--
Cheers
JulieD

Hi, thanks, but I couldn't get this to work.

--

Dave Peterson

Hi Dave

thanks for this :)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Dave Peterson" wrote in message
...
I think JulieD meant this:
=VLOOKUP(B3,look!$A$1:$B$22,2,0)

(She's returning the second column and forgot to increase the number of
columns.)

If you want to read more about =vlookup() and =index(match()), take a look
at
Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
http://www.contextures.com/xlFunctions03.html

JDT wrote:

On Fri, 15 Apr 2005 21:42:33 +0800, "JulieD"
wrote:

Hi

try
=VLOOKUP(B3,look!$A$1:$A$22,2,0)

--
Cheers
JulieD

Hi, thanks, but I couldn't get this to work.

--

Dave Peterson