Complex lookup

[horrellbt01]

I am trying to figure out a rather complex lookup.

Here is an example set of data.


A B C D E F

1

2

3

4 X

5 2 3


What i need to do is the following:

If there is an X in Range B3:F5, then I want to return whatever is in column A of that same row and row one of the same column.
So in my example above, there is an X in D4. I would want to return the value of A4 in one cell, and D1 in another. So probably looking at 2 different formulas.

Thought maybe INDEX and MATCH would work, but so far have not been able to work it out.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			Column 1	Column 2	Column 3	Column 4	Column 5			List of Result 1	List of Result 2
2	Row 1		1	3	7	9	2			Row 2	Column 5
3	Row 2		0	0	3	2	X			Row 3	Column 1
4	Row 3		X	X	1	5	8			Row 3	Column 2
5	Row 4		6	0	0	6	5			Row 5	Column 4
6	Row 5		0	1	8	X	7
Norfolk

Cell Formulas
Range	Formula
J2	=INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/($C$2:$G$6="X"),ROWS($A$1:$A1)))
K2	=INDEX($C$1:$G$1,AGGREGATE(15,6,(COLUMN($A$2:$G$2)-COLUMN($A$2)+1)/(INDEX($C$2:$G$6,MATCH(J2,$A$2:$A$6,0),0)="X"),COUNTIF($J$2:J2,J2)))

 

[DanteAmor]

They are the same result, it is only another order:

Mine

Result 2 -1
Result 2 -2
Result 2 -4
Result 2 -5



Yours

Result 2 -5
Result 2 -1
Result 2 -2
Result 2 -4


I don't understand what order you need.

The order I placed is in order of appearance (left to right), and the first to appear is that of column C, then D, then F and last G.

The same for results 1, the order is from top to bottom.


I guess you want a search from left to right and top to bottom.
C2,D2,E2,F2,G2 then C3,D3,E3,F3,G3, etc.

 

[horrellbt01]

This worked perfectly, even after I modified it to my data set (meaning I did not screw it up!)

Thank you very much!

How about

	A	B	C	D	E	F	G	H	I	J	K
Column 1	Column 2	Column 3	Column 4	Column 5	List of Result 1	List of Result 2
Row 1	Row 2	Column 5
Row 2	X	Row 3	Column 1
Row 3	X	X	Row 3	Column 2
Row 4	Row 5	Column 4
Row 5	X

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Norfolk​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/($C$2:$G$6="X"),ROWS($A$1:$A1)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=INDEX($C$1:$G$1,AGGREGATE(15,6,(COLUMN($A$2:$G$2)-COLUMN($A$2)+1)/(INDEX($C$2:$G$6,MATCH(J2,$A$2:$A$6,0),0)="X"),COUNTIF($J$2:J2,J2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

You're welcome & thanks for the feedback.

One thing I should have mentioned, is that it will only work if the values in col A are unique.

 

[DanteAmor]

Try this:

I did some tests and it works even with duplicates in column A.


<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:89.35px;" /><col style="width:14.26px;" /><col style="width:92.2px;" /><col style="width:92.2px;" /><col style="width:92.2px;" /><col style="width:92.2px;" /><col style="width:92.2px;" /><col style="width:23.76px;" /><col style="width:23.76px;" /><col style="width:141.62px;" /><col style="width:104.55px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td style="font-weight:bold; text-align:center; ">Result 2 -1</td><td style="font-weight:bold; text-align:center; ">Result 2 -2</td><td style="font-weight:bold; text-align:center; ">Result 2 -3</td><td style="font-weight:bold; text-align:center; ">Result 2 -4</td><td style="font-weight:bold; text-align:center; ">Result 2 -5</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; text-align:center; ">List of Result 1</td><td style="font-weight:bold; text-align:center; ">List of Result 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Result 1 - 1</td><td > </td><td style="text-align:center; ">1</td><td style="text-align:center; ">3</td><td style="text-align:center; ">7</td><td style="text-align:center; ">9</td><td style="text-align:center; ">2</td><td > </td><td > </td><td >Result 1 - 2</td><td >Result 2 -5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Result 1 - 2</td><td > </td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">3</td><td style="text-align:center; ">2</td><td style="background-color:#ffff00; text-align:center; ">X</td><td > </td><td > </td><td >Result 1 - 3</td><td >Result 2 -1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Result 1 - 3</td><td > </td><td style="background-color:#ffff00; text-align:center; ">X</td><td style="background-color:#ffff00; text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">5</td><td style="text-align:center; ">8</td><td > </td><td > </td><td >Result 1 - 3</td><td >Result 2 -2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Result 1 - 3</td><td > </td><td style="text-align:center; ">6</td><td style="background-color:#ffff00; text-align:center; ">X</td><td style="background-color:#ffff00; text-align:center; ">X</td><td style="text-align:center; ">6</td><td style="text-align:center; ">5</td><td > </td><td > </td><td >Result 1 - 3</td><td >Result 2 -2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Result 1 - 5</td><td > </td><td style="text-align:center; ">0</td><td style="text-align:center; ">1</td><td style="text-align:center; ">8</td><td style="background-color:#ffff00; text-align:center; ">X</td><td style="text-align:center; ">7</td><td > </td><td > </td><td >Result 1 - 3</td><td >Result 2 -3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Result 1 - 5</td><td >Result 2 -4</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >J2</td><td >{=IFERROR(INDEX($A$1:$A$6,SMALL(IF($C$2:$G$6="X",ROW($B$2:$B$6)),ROWS($I$2:I2))),"")}</td></tr><tr><td >K2</td><td >{=INDEX($A$1:$G$1,0,SMALL(IF($A$2:$A$6=J2,IF($C$2:$G$6="X",COLUMN($C$1:$G$1))),COUNTIF($J$2:J2,J2)))}</td></tr></table></td></tr></table>