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.

A B C D E F 1 47.00 48.00 49.00 date value 2 1/1/2021 3/1/2018 5/1/2019 1/1/2021 47.00 3 3/1/2018 48.00 4 3/1/2018 48.00 5 5/1/2019 49.00 6 5/1/2019 49.00 <colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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)