finding corresponding values in an array

[kpm30519]

Hi--

I have a list of values in columns, organized by date. I'm trying to return the first instance of a specific value no matter the column it's in:

[TABLE="width: 312"]
<tbody>[TR]
[TD] Date[/TD]
[TD]Box1[/TD]
[TD]Box2[/TD]
[TD]Box3[/TD]
[TD]Box4[/TD]
[TD]Box5[/TD]
[/TR]
[TR]
[TD]10/31/17
[/TD]
[TD]31[/TD]
[TD]53[/TD]
[TD]6[/TD]
[TD]28[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]11/03/17[/TD]
[TD]69[/TD]
[TD]10[/TD]
[TD]61[/TD]
[TD]42[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]11/07/17[/TD]
[TD]1[/TD]
[TD]68[/TD]
[TD]69[/TD]
[TD]60[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]11/10/17[/TD]
[TD]6[/TD]
[TD]38[/TD]
[TD]42[/TD]
[TD]23[/TD]
[TD]69[/TD]
[/TR]
</tbody>[/TABLE]

If I search for the value 69, my result should be 11/03/17 even though the value is in 3 columns.

Thanks for your help, I'm loosing my hair over this.

 

[My Aswer Is This]

Is the date in column A if not what column
And what is Box1 and Box 2 etc.

I do not understand Box

Do you mean column B Column C

Excel has nothing named Box

And where do you want the results?

 

[Fluff]

Hi & welcome to MrExcel.
If you had this

Excel 2013/2016
	C	D	E	F	G	H
17	Date	Box1	Box2	Box3	Box4	Box5
18	10/31/2017	31	69	6	28	52
19	11/03/2017	69	10	61	42	22
20	11/07/2017	1	68	69	60	54
21	11/10/2017	6	38	42	23	69
List

Which would you consider to be the "first". The Blue one because of the row, or the green one because of the column?

 

[Fluff]

Assuming you want the earliest date

Excel 2013/2016
	A	B	C	D	E	F	G
1	Date	Box1	Box2	Box3	Box4	Box5	69
2	10/31/2017	31	69	6	28	52	10/31/2017
3	11/03/2017	69	10	61	42	22
4	11/07/2017	1	68	67	60	54
5	11/10/2017	6	38	42	23	69
List

Cell Formulas
Range	Formula
G2	{=INDEX(A2:A5,MIN(IF(B2:F5=G1,ROW(B2:F5)-1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kpm30519]

Assuming you want the earliest date

Excel 2013/2016

	A	B	C	D	E	F	G
Date	Box1	Box2	Box3	Box4	Box5
10/31/2017	10/31/2017

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

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

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

[TD="align: right"]31[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]52[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11/03/2017[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11/07/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11/10/2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"][/TD]

</tbody>

List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=INDEX(A2:A5,MIN(IF(B2:F5=G1,ROW(B2:F5)-1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

THANK YOU FLUFF!!!

It was very late when I posted, and I noticed I made some small mistakes, but with your help I was able to use "=INDEX(C$2:C$1048576,MAX(IF(D$2:H$1048576=AE2,ROW(D$2:H$1048576)-1)))." I realized my dates were in reverse order, so I used MAX, and it's PERFECT!!

Thanks Again!

 

[Fluff]

Glad to help & thanks for the feedback.
I would however recommend you reduce the size of the arrays, running an array formula on entire columns tends to be very slow.