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.

 

[horrellbt01]

Sorry - My example did not keep the formatting. Working on a picture now.

 

[Special-K99]

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H
1	A	B	C	D	E	F
2	1						Column	D
3	2						Row	4
4	3
5	4			X
Norfolk

Cell Formulas
Range	Formula
H2	=INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($A$2:$F$2)-COLUMN($A$2)+1)/($B$2:$F$5="X"),1))
H3	=INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/($B$2:$F$5="X"),1))

 

[horrellbt01]

Using the attached image.
Since there an X in G3, I would want A3 in J2, and G1 in K2
Next X would be in C4. So I would want the value of A4 in J3 and C1 in K3.
Then keep going with the X's in D4 and F7, etc.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[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]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result 2[/TD]
[TD]Result 2[/TD]
[TD]Result 2[/TD]
[TD]Result 2[/TD]
[TD]Result 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]List of Result 1[/TD]
[TD]List of Result 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Result 1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Result 1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Result 1[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Result 1[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Result 1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]X[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

To return column header

=INDEX(B1:F1,MAX(IF(B3:F5="X",COLUMN(B1:F1)-COLUMN(B1)+1)))
Array formula, use Ctrl-Shift-Enter

To return row header

=INDEX(A3:A5,MAX(IF(B3:F5="X",ROW(A3:A5)-ROW(A3)+1)))
Array formula, use Ctrl-Shift-Enter

UPDATE: My solution does not allow for duplicate Xs in the grid

 

[horrellbt01]

Special-K99 otherwise it would have worked!
Having those multiple X's really throws a wrench into it.

 

[horrellbt01]

Fluff - Can this be adjusted for Multiple X's like my example below?

 

[DanteAmor]

Try this

<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:107.41px;" /><col style="width:26.61px;" /><col style="width:67.49px;" /><col style="width:67.49px;" /><col style="width:67.49px;" /><col style="width:67.49px;" /><col style="width:67.49px;" /><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 >Result 2 -1</td><td >Result 2 -2</td><td >Result 2 -3</td><td >Result 2 -4</td><td >Result 2 -5</td><td > </td><td > </td><td >List of Result 1</td><td >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 -1</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="text-align:center; ">X</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; " >4</td><td >Result 1 - 3</td><td > </td><td style="text-align:center; ">X</td><td style="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 -4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Result 1 - 4</td><td > </td><td style="text-align:center; ">6</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">6</td><td style="text-align:center; ">5</td><td > </td><td > </td><td >Result 1 - 5</td><td >Result 2 -5</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="text-align:center; ">X</td><td style="text-align:center; ">7</td><td > </td><td > </td><td > </td><td > </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 > </td><td > </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 >{=IFERROR(INDEX($A$1:$G$1,0,SMALL(IF($C$2:$G$6="X",COLUMN($C$1:$G$1)),ROWS($I$2:J2))),"")}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


Put the formulas in J2 and K2, then copy down.

 

[horrellbt01]

That is really close, but the Result 2 formula looks like it is off just a little bit.

The List of Results should be the following based on the chart below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List of Result 1[/TD]
[TD]List of Result 2[/TD]
[/TR]
[TR]
[TD]Result 1-2[/TD]
[TD]Result 2-5[/TD]
[/TR]
[TR]
[TD]Result 1-3[/TD]
[TD]Result 2-1[/TD]
[/TR]
[TR]
[TD]Result 1-3[/TD]
[TD]Result 2-2[/TD]
[/TR]
[TR]
[TD]Result 1-5[/TD]
[TD]Result 2-4[/TD]
[/TR]
</tbody>[/TABLE]

Try this

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

<tbody>
[TD="align: center"]1[/TD]

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

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

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

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]

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

[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]

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

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

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

[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]7[/TD]

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

</tbody>

Cell Array Formula J2 {=IFERROR(INDEX($A$1:$A$6,SMALL(IF($C$2:$G$6="X",ROW($B$2:$B$6)),ROWS($I$2:I2))),"")} K2 {=IFERROR(INDEX($A$1:$G$1,0,SMALL(IF($C$2:$G$6="X",COLUMN($C$1:$G$1)),ROWS($I$2:J2))),"")} <tbody> </tbody>

<tbody>
</tbody>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


Put the formulas in J2 and K2, then copy down.