VLookup

[futbol1097]

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

 

[XLGibbs]

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]

Assumptions:

A1:B8 contains your source table

E1:L1 contains your column headers/labels for your results table

D2:D5 contains your column headers/labels for your results table

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!

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	X	Y	Concat		Green	Red	Yellow	Orange	Black	Red	Violet	Blue
2	101	Green	101#Green	101	1	1	0	0	0	1	0	1
3	101	Red	101#Red	201	1	0	1	0	0	0	0	0
4	101	Blue	101#Blue	301	0	0	0	0	1	0	0	0
5	201	Green	201#Green	401	0	1	0	0	0	1	1	0
6	201	Yellow	201#Yellow
7	301	Black	301#Black
8	401	Red	401#Red
9	401	Violet	401#Violet
Sheet1

C2, copied down:

=A2&"#"&B2

E2, copied across then down:

=ISNUMBER(MATCH($D2&"#"&E$1,$C$2:$C$9,0))+0

If so desired, custom format the results area as:

[=0]"No";[=1]"Yes"

 

[futbol1097]

re-sorting this table

Thanks guys - worked great. Ok, here is what I have now:

Book7
	A	B	C	D	E
1	Address	1ST	2ND	3RD	POOL
2	101	Yes	No	NO	No
3	102	Yes	No	NO	Yes
4	103	No	Yes	NO	No
5	104	No	No	Yes	No
6	105	Yes	No	NO	No
7	106	Yes	No	NO	Yes
8	107	No	Yes	NO	No
9	108	No	Yes	NO	Yes
10	109	Yes	No	NO	No
Sheet1

Now I want to create a new table that will do this:

Book7
	H	I	J	K
1	Address	Level	Pool
2	101	1ST
3	102	1ST	Pool
4	103	2ND
5	104	3rd
6	105	1st
7	106	1st
8	107	2nd
9	108	2nd	Pool
10	109	2nd
11	110	3rd	Pool
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]

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!