#### futbol1097

##### New Member

- Joined
- Dec 14, 2005

- Messages
- 32

A B

101 Green

101 Red

101 Blue

201 Green

201 Yellow

301 Black

401 Red

401 Violet

I am trying to write a formula on another page that for each group (101, 201, 301, 401) if both criteria are met (ex: "101" and "Green") in the original table, i want the formula to return the word "Yes", otherwise "No".

In essence I am creating a new table horizontally this time:

Green Red Yellow Orange Black Red Violet Blue

101 Yes Yes No No No No No Yes

201 Yes No yes No No No No No

301 No No No NO Yes No No No

401 No Yes No No No No Yes No

I need this because I am then doing a calculation that will add a certain value to a sum if the word Yes or No is written there. This is the intermediate sorting step.

How do I use a lookup formula to do this? The Vlookup only finds the first occurence of a lookup value. However I want it to return every occurence of the value, (ex: all 101's, or all 201's)

Is this possible to have a vlookup pull multiple values?

Option 2:

If this is not possible, could I write formulas that rearranges a new table horizontally:

D E F G

101 Green Red Blue

201 Green Yellow

301 Black

401 Red Violet

Now I could do a standard Vlookup based on the column D criteria. Assuming that new table (D:G) is already populated with the correct "lookup values" (one per row) in column D, how do I write a lookup formula that will : lookup B1, then B2, then B3, then B4, then B5....and then return those values horizontally in the new table, but automatically allocated to the proper row based on the lookup value)? For example, all column B values that are 101 will be put on Row 1 (E1, F1, G1), all values in B with 201, get put on row 2 (E2, F2), etc. I would like to be able to copy this formula down and across since it is a large table!

Thanks so much,

E