VLookup

futbol1097

New Member
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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

XLGibbs

Well-known Member
Look into the AND() function, you can nest that in your criteria considerations I believe...

If you use the HTML maker and post your sheet it may be easier for others and myself to offer other solutions..

Domenic

MrExcel MVP
Assumptions:

Formula:

E2, copied down and across:

=IF(SUMPRODUCT(--(\$A\$1:\$A\$8=\$D2),--(\$B\$1:\$B\$8=E\$1))*(COUNTIF(\$E\$1:E\$1,E\$1)<=1),"Yes","No")

Hope this helps!

futbol1097

New Member
re-sorting this table

Thanks guys - worked great. Ok, here is what I have now:
Book7
ABCDE
2101YesNoNONo
3102YesNoNOYes
4103NoYesNONo
5104NoNoYesNo
6105YesNoNONo
7106YesNoNOYes
8107NoYesNONo
9108NoYesNOYes
10109YesNoNONo
Sheet1

Now I want to create a new table that will do this:
Book7
HIJK
21011ST
31021STPool
41032ND
51043rd
61051st
71061st
81072nd
91082ndPool
101092nd
111103rdPool
Sheet1

I want the formula in column I to look up the Address in column A (ex: 104), then search across columns B:D to find where "Yes" is written. When "Yes" is found, I want it to return the word that is written in the respective Row 1 header. For example, 104 is on the "3rd" level b/c the "Yes" is in column D - so in cell I5 I want it to write whatever word is in D1 ("3rd"). This will allow me to consolidate rows B:D onto one column, because I am concerned about where the "Yes" is.

In column J, I want the formula to search two criteria: 1) search for the lookup value (104) 2) find the column w/ header "Pool" - when there is a "Yes" there, I want it to return the word "Pool"; otherwise leave the cell blank.

Thanks for the help guys,
E

Domenic

MrExcel MVP
Try...

I2, copied down:

=INDEX(\$B\$1:\$D\$1,MATCH("Yes",INDEX(\$B\$2:\$D\$10,MATCH(H2,\$A\$2:\$A\$10,0),0),0))

J2, copied down:

=IF(INDEX(\$E\$2:\$E\$10,MATCH(H2,\$A\$2:\$A\$10,0))="Yes","Pool","")

or
=IF(INDEX(\$A\$2:\$E\$10,MATCH(\$H2,\$A\$2:\$A\$10,0),MATCH(J\$1,\$A\$1:\$E\$1,0))="Yes",J\$1,"")

Hope this helps!

Replies
9
Views
182
Replies
9
Views
137
Replies
6
Views
153
Replies
0
Views
114
Replies
5
Views
79