@Vlookup w/ Multiple answers

[Ben]

Hi,

I was wondering if there is a way to do a v-lookup that
will bring up all the matching answers. Example
A B C
Clothing Type Color Size
1 Pants WH 32
2 T-shirt BL XL
3 Shorts GR 22
4 T-shirt BR SM
5 Pants GR 26
6 Belt BU LA
7 T-shirt BL ME

Can you do a V-lookup that will search for all the T-
shirts in column A and brig up all the corresponding
answers?

Currently it will only bring up the first T-shirt in the
list.

I Would like to know how to bring them all up and or how
to hit a ?next button? so it will keep going through the
list etc...

Thanks,
Ben

 

[RagDyeR]

This old post might help.

Replace "BadgeNumber" with your "ClothingType".

http://tinyurl.com/la7v
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi,

I was wondering if there is a way to do a v-lookup that
will bring up all the matching answers. Example
A B C
Clothing Type Color Size
1 Pants WH 32
2 T-shirt BL XL
3 Shorts GR 22
4 T-shirt BR SM
5 Pants GR 26
6 Belt BU LA
7 T-shirt BL ME

Can you do a V-lookup that will search for all the T-
shirts in column A and brig up all the corresponding
answers?

Currently it will only bring up the first T-shirt in the
list.

I Would like to know how to bring them all up and or how
to hit a ?next button? so it will keep going through the
list etc...

Thanks,
Ben

 

[Ben]

RD,

I get a blank page when I try and use the URL Provided.
It redirects to the following page.

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-

Ben

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups("T-shirt",A1:C7,2) array entered into a column at least
sufficiently large to accommodate the output.

Alan Beban

 

[Ben]

Alan,

This looks like what I need. How do I use it?
Thanks,
ben

 

[Harlan Grove]

I was wondering if there is a way to do a v-lookup that
will bring up all the matching answers. Example
A B C
Clothing Type Color Size
1 Pants WH 32
2 T-shirt BL XL
3 Shorts GR 22
4 T-shirt BR SM
5 Pants GR 26
6 Belt BU LA
7 T-shirt BL ME

Can you do a V-lookup that will search for all the T-
shirts in column A and brig up all the corresponding
answers?

Currently it will only bring up the first T-shirt in the
list.

I Would like to know how to bring them all up and or how
to hit a ?next button? so it will keep going through the
list etc...

If you'd be willing to do this interactively using buttons to move between
records, perhaps an Autofilter would work best for you. Select the entire table,
including top row field names, then Data > Filter > Autofilter from the menu.
Then use the drop down in the top-left cell to select any of the clothing types.

To get the same results using only built-in formulas, I'll assume you'd enter
the clothing type in cell G2. The topmost matching color and size would be in H2
and I2, respectively.

H2: [array formula]
=IF(COUNTIF(INDEX(YourTable,0,1),$G$2)>ROW()-ROW($G$2),
INDEX(YourTable,SMALL(IF(INDEX(YourTable,0,1)=$G$2,
ROW(YourTable)-CELL("Row",YourTable)+1),ROW()-ROW($G$2)+1),2),"")

I2: [array formula]
=IF(COUNTIF(INDEX(YourTable,0,1),$G$2)>ROW()-ROW($G$2),
INDEX(YourTable,SMALL(IF(INDEX(YourTable,0,1)=$G$2,
ROW(YourTable)-CELL("Row",YourTable)+1),ROW()-ROW($G$2)+1),3),"")

Then select H2:I2 and fill down as far as needed.

 

[Ben]

Thanks Harlan!

Ben

-----Original Message-----

I was wondering if there is a way to do a v-lookup that
will bring up all the matching answers. Example
A B C
Clothing Type Color Size
1 Pants WH 32
2 T-shirt BL XL
3 Shorts GR 22
4 T-shirt BR SM
5 Pants GR 26
6 Belt BU LA
7 T-shirt BL ME

Can you do a V-lookup that will search for all the T-
shirts in column A and brig up all the corresponding
answers?

Currently it will only bring up the first T-shirt in the
list.

I Would like to know how to bring them all up and or how
to hit a ?next button? so it will keep going through the
list etc...

If you'd be willing to do this interactively using buttons to move between
records, perhaps an Autofilter would work best for you. Select the entire table,
including top row field names, then Data > Filter > Autofilter from the menu.
Then use the drop down in the top-left cell to select any of the clothing types.

To get the same results using only built-in formulas, I'll assume you'd enter
the clothing type in cell G2. The topmost matching color and size would be in H2
and I2, respectively.

H2: [array formula]
=IF(COUNTIF(INDEX(YourTable,0,1),$G$2)>ROW()-ROW($G$2),
INDEX(YourTable,SMALL(IF(INDEX(YourTable,0,1)=$G$2,
ROW(YourTable)-CELL("Row",YourTable)+1),ROW()-ROW($G$2) +1),2),"")

I2: [array formula]
=IF(COUNTIF(INDEX(YourTable,0,1),$G$2)>ROW()-ROW($G$2),
INDEX(YourTable,SMALL(IF(INDEX(YourTable,0,1)=$G$2,
ROW(YourTable)-CELL("Row",YourTable)+1),ROW()-ROW($G$2) +1),3),"")

Then select H2:I2 and fill down as far as needed.