futbol1097
New Member
- Joined
- Dec 14, 2005
- Messages
- 32
I am trying to do a Vlookup on a table that has multiple cells for each "lookup value". This may be called a double lookup. For example:
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
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