How do i use vlookup to find more than 1 entry
 

I want to use vlookup to find more than one entry from a table.
e.g
red 1
red 2
blue 12
blue 45

I want to run vlookup so that it will look up red and return 1 and then when
i run it again i want it to return 2, likewise when i get vlookup to lookup
blue i want it to return 12 and then when i run it again i want it to return
45.
Thanks.

Hi

Add a column left to your table. When p.e. your data are now in range
B2:C100 (with headers in row 1), then into cell A2 enter the formula
=IF($B2="","",$B2 & COUNTIF($B$2:$B2,$B2))
and copy it down p.e. to range A2:A1000 (you have 900 spare rows for future
data). (You can hide the column A now, when you want.)

When p.e. into cell H1 you enter the color name, and want all values for
this color displayed below, then into H2 enter the formula
=IF(ISERROR(VLOOKUP($H$1 & ROW()-1,$A$2:$C$1000,3,0)),"",VLOOKUP($H$1 &
ROW()-1;$A$2:$C$1000,3,0))
and copy it down for some reasonable number of rows (depends how much
possible entries estimate you for a color maximally). It's done!

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Shaum" wrote in message
...
I want to use vlookup to find more than one entry from a table.
e.g
red 1
red 2
blue 12
blue 45

I want to run vlookup so that it will look up red and return 1 and then
when
i run it again i want it to return 2, likewise when i get vlookup to
lookup
blue i want it to return 12 and then when i run it again i want it to
return
45.
Thanks.

Is there any reason you couldn't set your table up with two columns for
numbers, so it'd be like this:

Red 1 2
Blue 12 45

Then your first VLOOKUP could get the 2d column and the next VLOOKUP could
get the 3d column


"Shaum" wrote in message
...
I want to use vlookup to find more than one entry from a table.
e.g
red 1
red 2
blue 12
blue 45

I want to run vlookup so that it will look up red and return 1 and then
when
i run it again i want it to return 2, likewise when i get vlookup to
lookup
blue i want it to return 12 and then when i run it again i want it to
return
45.
Thanks.

Shaum wrote:
I want to use vlookup to find more than one entry from a table.
e.g
red 1
red 2
blue 12
blue 45

I want to run vlookup so that it will look up red and return 1 and then when
i run it again i want it to return 2, likewise when i get vlookup to lookup
blue i want it to return 12 and then when i run it again i want it to return
45.
Thanks.

You might want to consider making available to your workbook the
functions in the freely downloadable file at
http://home.pacbell.net/beban.

Vlookups("red", table_ref,2) will then return a vertical array of the
values corresponding to red.

In VBA Vlookups("red",table_ref,2)(2,1) would return 2, and
Vlookups("blue",table_ref,2)(1,1) would return 12.

On the worksheet =INDEX(VLOOKUPS("blue",table_ref,2,False),2) would
return 45.

Alan Beban