Excel Magic Trick 1259: Can VLOOKUP Lookup Duplicates? Yes! Helper Column or Array Formula?

Learn how to use two lookup values to lookup an item in a table:
1. (00:11) Problem Introduction and discussion of default behavior for VLOOKUP for Exact Match.
2. (01:00) Helper Colum Solution with Joined Values to create Unique Identifier in First Column. VLOOKUP and Joined Lookup-Value.
3. (03:01) Single Cell Array Formula with INDEX and MATCH functions and an array operation using the join symbol. Array Formula that requires Ctrl + Shift + Enter.
4. Array Formula that does NOT requires Ctrl + Shift + Enter. Using INDEX array argument to avoid Ctrl + Shift + Enter.

[bestscenes]

You have saved me. Excellent Video. Thanks a million.

[kbell]

Used this again in another project with nearly 3K employee names--myriad of duplicate common first and last names--worked perfectly. Great resource video!

[ArthurBorshenko]

I didn't know that you can leave the row_num of INDEX empty?! Even the tooltip doesn't show it as optional. This is indeed an Excel Magic Trick. Thanks Mike. Top notch video as always.

[azazel_]

Indeed, the duplicates are a nitemare, in my case appear by hundreds times.
I solved using unique id`s (the date, and/or codes, for instance.) attached for each task/activity, and of course, a lot of excel function (vllook, if, and, min, max..etc.), dedicated sheets.
Respect for your lessons, are excelent inspiration source.

[tops]

This is what I'm looking for. Thank you very much. Very huge help.

[abhaygadiya]

As an alternative with various options now available in Power Query for "Joins" this is pretty easy and can be refreshed with new data being added.

[yashsomaiya]

Superb video must watch if you want to become master in vlookup.

[kbell]

Awesome video content--helped clear confusion with duplicate values inside data set.

[LeeDixon]

Many thanks for your time and another excellent contribution. As always I learnt some very useful tips. I will practice with Index and Match functions so that they don't seem so complex to me.

[ashaydwivedi]

Your Videos Are So Famous That It got 66 Views After Seconds Of Uploading. It Was My B'Day On 18 Jan. Bought Your Book Ctrl Shift Enter

[JS-jiou]

Very helpful. You are a master! Thank you

[mohammedsaqib]

I am looking for this and u perfectly explain thanks

[tarteambarish]

At 02:35, while watching this video, I had to hit the ATL TAB and go check my Outlook.
Watch the video again and listen keenly at 02:35.
Moreover, I want to highlight that closing Parenthesis is not really needed. just hit CTRL ENTER

[Hopefulgoingforward]

Dude, thank you for posting this video!

[TimSheets]

index, match, indea...that's pretty cool.

[vitorpratte]

My spreadsheet has repeated values ​​in the first column and I can not delete it. I need my col_index_number to display only the largest value (Date, in this case)

[bathinimallikarjuna]

Hi Sir,
How can i do results lookup results in dropdown so i can select which i need for duplicates only if signal result it would show automatically if there is multiple results i need to select from dropdown.
Please help out this.

[WHS]

Thanx for this video.

But what is the most efficient method for Excel to do this in massive workbooks with lots of data?

I use MATCH( 1, (rangeA = x ) * (rangeB = y) * ..., 0) a lot to determine a row number in a table based on multible variables, but it uses a lot of calculation time.

Is there a "most efficient" way?

[ryanjefferis]

Is it possible to have a drop down list for the server name and then a dependent drop down for event? Meaning create a drop down list with the server names, based on the choice have a drop down with the events that correspond to the server name selected. Example drop down choose "ZZR45678A" and have the other drop down automatically have only two options EVENT - 1 and EVENT - 2?

[PawanKumar-wnde]

i did not find the attachments of this book