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

smgomez

New Member
Joined
Nov 13, 2009
Messages
43
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.

ABCDEF
147.0048.0049.00datevalue
21/1/20213/1/20185/1/20191/1/202147.00
33/1/201848.00
43/1/201848.00
55/1/201949.00
65/1/201949.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>
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
But you already have a lookup table and vlookup is the best solution here:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">47</td><td style="text-align: right;;">48</td><td style="text-align: right;;">49</td><td style="text-align: right;;"></td><td style=";">date</td><td style=";">value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1/1/2021</td><td style="text-align: right;;">3/1/2018</td><td style="text-align: right;;">5/1/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2021</td><td style="text-align: right;;">47</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3/1/2018</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3/1/2018</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5/1/2019</td><td style="text-align: right;;">49</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5/1/2019</td><td style="text-align: right;;">49</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A1,CHOOSE(<font color="Red">{2,1},$E$2:$E$6,$F$2:$F$6</font>),2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

smgomez

New Member
Joined
Nov 13, 2009
Messages
43
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
=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)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,507
Messages
5,529,268
Members
409,859
Latest member
emperorgenghiskhan
Top