vlookup

I have two worksheets one with the prices for lookup and one that I would like to type in the part # and then it looks up the price from the other. I can get it to do it for a single cell if it matches the exact row but not for numerous.
=VLOOKUP(B1:B10,mat!A1:C157,3,FALSE) It will only fill in B1 if the number matches. What am I doing wrong
Thanks in advance

vw

You are asking Vlookup to look for a range of cells, this
is possible in an array formula but it will only return
the first value of the array if typed only into one cell.

See if this works, say yo have Items on B1:B10 that you
want to get prices for, on cell C1 Type:

=VLOOKUP(B1,mat!$A$1:$C$157,3,FALSE)

And then select from C1 to C10 and fill down, this will
change automatically the B1 part because its relative (no
$ signs) but keep fix the $A$1:$C$157 part because of the
dollar signs...

See if that helps, if not... post back in the same
thread...

Cheers
Juan

-----Original Message-----
I have two worksheets one with the prices for lookup and
one that I would like to type in the part # and then it
looks up the price from the other. I can get it to do it
for a single cell if it matches the exact row but not for
numerous.
=VLOOKUP(B1:B10,mat!A1:C157,3,FALSE) It will only fill
in B1 if the number matches. What am I doing wrong
Thanks in advance
.

Juan - Here is a copy of the one worksheet. It works for #10000 but won't lookup the cost of the following #'s. We have different #'s for different material. Here is the formula I typed =VLOOKUP(B1,mat!A1:B157,2,FALSE) I thought that I could type B1:B5 but that did not work as it returned 2.62. Please help.
Thanks vw

Cross-Arm 10000 2.62
12100 2.62
12100 #N/A
13400 #N/A
13700 #N/A


--
vw


"Juan Sanchez" wrote:


vw

You are asking Vlookup to look for a range of cells, this
is possible in an array formula but it will only return
the first value of the array if typed only into one cell.

See if this works, say yo have Items on B1:B10 that you
want to get prices for, on cell C1 Type:

=VLOOKUP(B1,mat!$A$1:$C$157,3,FALSE)

And then select from C1 to C10 and fill down, this will
change automatically the B1 part because its relative (no
$ signs) but keep fix the $A$1:$C$157 part because of the
dollar signs...

See if that helps, if not... post back in the same
thread...

Cheers
Juan

-----Original Message-----
I have two worksheets one with the prices for lookup and
one that I would like to type in the part # and then it
looks up the price from the other. I can get it to do it
for a single cell if it matches the exact row but not for
numerous.
=VLOOKUP(B1:B10,mat!A1:C157,3,FALSE) It will only fill
in B1 if the number matches. What am I doing wrong
Thanks in advance
.

VW,
Make sure this part of the formula covers all your prices:
=VLOOKUP(B1, - mat!A1:B157 - ,2,FALSE)
and make it absolute mat!$A$1:$C$157
Put the dollar signs around

Harry
"vw" wrote:

Juan - Here is a copy of the one worksheet. It works for #10000 but won't lookup the cost of the following #'s. We have different #'s for different material. Here is the formula I typed =VLOOKUP(B1,mat!A1:B157,2,FALSE) I thought that I could type B1:B5 but that did not work as it returned 2.62. Please help.
Thanks vw

Cross-Arm 10000 2.62
12100 2.62
12100 #N/A
13400 #N/A
13700 #N/A


--
vw


"Juan Sanchez" wrote:


vw

You are asking Vlookup to look for a range of cells, this
is possible in an array formula but it will only return
the first value of the array if typed only into one cell.

See if this works, say yo have Items on B1:B10 that you
want to get prices for, on cell C1 Type:

=VLOOKUP(B1,mat!$A$1:$C$157,3,FALSE)

And then select from C1 to C10 and fill down, this will
change automatically the B1 part because its relative (no
$ signs) but keep fix the $A$1:$C$157 part because of the
dollar signs...

See if that helps, if not... post back in the same
thread...

Cheers
Juan

-----Original Message-----
I have two worksheets one with the prices for lookup and
one that I would like to type in the part # and then it
looks up the price from the other. I can get it to do it
for a single cell if it matches the exact row but not for
numerous.
=VLOOKUP(B1:B10,mat!A1:C157,3,FALSE) It will only fill
in B1 if the number matches. What am I doing wrong
Thanks in advance
.

vw

If it shows #N/A it means it's not finding the item, make
sure that all the items you are looking for are actually
in the looked up matrix...

Use the dollar signs on the looked up matrix so that it is
fix when you fill down...

Cheers

Juan


-----Original Message-----
Juan - Here is a copy of the one worksheet. It works for
#10000 but won't lookup the cost of the following #'s. We
have different #'s for different material. Here is the
formula I typed =VLOOKUP(B1,mat!A1:B157,2,FALSE) I
thought that I could type B1:B5 but that did not work as
it returned 2.62. Please help.
Thanks vw

Cross-Arm 10000 2.62
12100 2.62
12100 #N/A
13400 #N/A
13700 #N/A


--
vw


"Juan Sanchez" wrote:


vw

You are asking Vlookup to look for a range of cells,
this
is possible in an array formula but it will only return
the first value of the array if typed only into one
cell.

See if this works, say yo have Items on B1:B10 that you
want to get prices for, on cell C1 Type:

=VLOOKUP(B1,mat!$A$1:$C$157,3,FALSE)

And then select from C1 to C10 and fill down, this will
change automatically the B1 part because its relative
(no
$ signs) but keep fix the $A$1:$C$157 part because of
the
dollar signs...

See if that helps, if not... post back in the same
thread...

Cheers
Juan

-----Original Message-----
I have two worksheets one with the prices for lookup
and
one that I would like to type in the part # and then it
looks up the price from the other. I can get it to do
it
for a single cell if it matches the exact row but not
for
numerous.
=VLOOKUP(B1:B10,mat!A1:C157,3,FALSE) It will only
fill
in B1 if the number matches. What am I doing wrong
Thanks in advance
.


.