find a value in a column with duplicates and return first value

[smgomez]

I want to find the value in A1 in column F and return the corresponding date in column E in cell A2 as shown below. I am trying to avoid creating a lookup table and vlookup to do it. Column E and F have duplicates so I just need the date upon the first match of the value.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]47.00[/TD]
[TD="class: xl67"]48.00[/TD]
[TD="class: xl67"]49.00[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]date[/TD]
[TD="class: xl66"]value[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl68"]1/1/2021[/TD]
[TD="class: xl68"]3/1/2018[/TD]
[TD="class: xl68"]5/1/2019[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl68"]1/1/2021[/TD]
[TD="class: xl67"]47.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"]3/1/2018[/TD]
[TD="class: xl67"]48.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl68"]3/1/2018[/TD]
[TD="class: xl67"]48.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl68"]5/1/2019[/TD]
[TD="class: xl67"]49.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl68"]5/1/2019[/TD]
[TD="class: xl67"]49.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

But you already have a lookup table and vlookup is the best solution here:

Excel 2010
	A	B	C	D	E	F
1	47	48	49		date	value
2	1/1/2021	3/1/2018	5/1/2019		1/1/2021	47
3					3/1/2018	48
4					3/1/2018	48
5					5/1/2019	49
6					5/1/2019	49
Sheet11

Cell Formulas
Range	Formula
A2	=VLOOKUP(A1,CHOOSE({2,1},$E$2:$E$6,$F$2:$F$6),2,0)

 

[smgomez]

ok, let me add additional info. The info in columns E and F are in a separate worksheet and I need to include the entire column versus rows 2-6 in the example above which is oversimplified. I tried to adjust per your formula recommendation as follows but it did not work, the result is #N/A: =VLOOKUP(A$1,CHOOSE({2,1},Sheet2!$B:$B,Sheet2!$A:$A),2,0) what is the {2,1} accomplishing in this formula and please advise how to make it work when the data is not in a range on another sheet potentially somewhere in the entire column. Thanks!

 

[sheetspread]

Don't use the entire column! That's over 1 million rows (or 65k+ in Excel 2003 and earlier) and very inefficient. If you actually have that much data Power BI is a far better approach. The formula looks up A1 in Sheet2!A:A and returns the adjacent value in Sheet2!B:B but be sure the range contains the same value--not the value with an extra space or stored as text while the other is a number.

 

[Marcelo Branco]

Try INDEX/MATCH

A2 copied across
=INDEX(Sheet1!$A:$A,MATCH(A$1,Sheet1!$B:$B,0))

M.

 

[sheetspread]

=VLOOKUP(A$1,CHOOSE({2,1},Sheet2!$B:$B,Sheet2!$A:$A),2,0)

If Marcelo's formula works for you then this should also:

=VLOOKUP(A$1,CHOOSE({2,1},Sheet2!$A:$A,Sheet2!$B:$B),2,0)

(It was backwards. The choose array reverses the two columns for a left lookup effect)