VLOOKUP or Index Match for me?

[huskerjhansen]

I've been struggling with a large amount of data and just when I thought I had it the limitation of VLOOKUP ruined my Aha! moment.

My 'RawData' sheet has multiple contacts per company and I wanted to organize the contacts by Company (Row) and by Title (Column)

What I am running into is the formula I am using, =VLOOKUP(A11,Table1,11,FALSE), returns only the first occurrence of information for that company. The rest of the contact info for that same company return false rather than the desired info.

I stumbled across an explanation that something like =INDEX(Table1,MATCH(A3,Table1,0)) may work but I just can't wrap my head around this new (to me) formula. I've attached a sample workbook to give you a better idea.

The format of Sheet2 is what I'm looking for. Any ideas on an easier way are welcome.

View attachment Practice.xlsm

 

[NBVC]

Having a hard time understanding what you are looking for. You have so many FALSE in both sheets, that I can't figure out what you need.

Can you show expected results and why?

 

[huskerjhansen]

With the help of a genius co worker I figured out it was Index Match that best fit me. The basic problem was getting many contacts from a third party into one excel doc in an organized and visually easy way to understand without having to manually copy and paste thousands of times. This worked because the third party used a consistent format for the data.

Our winning formula was =IFERROR(INDEX(Table1[Name],MATCH($A3&" "&E$1,Table1[Company+Sport],0)),"")

We created a new row1 and gave it the sport name and also essentially concatenated two non unique frequently used values [Company] and [Sport] into a 100% unique value.

For anyone who stumbles upon this later. I'll do my best to explain the formula.

IFERROR( - used to keep a ton of FALSE values off the page. IFNA works for Excel2013
INDEX(
Table1 - name of our table. Data must be in a table with headers for formula to work
[Name] - Column name on table signifying where data is coming from. Basically the important info that you want populated by the formula. I have three tweaked formulas to pull name, email, and phone.
MATCH(
$A3&" "&E$1, a statement combining column A of destination sheet and a value in row 1 of the destination sheet in one string to EXACTLY match the 100% unique value on the rawdata sheet. *This is one par of the formula that can probably stand to be cleaned up a bit.
Table1[Company+Sport] Column B in the rawdata table. Formed from A and C to create a 100% unique value used to match against the destination sheet
,0)) zero meansTable1[Company+Sport] and$A3&" "&E$1 must match exactly to return a value
,"") means that a non true statement will return a blank cell instead of FALSE

I hope someone else finds a use for something like this in the future. If you guys see something that can be cut out to make it easier, I'm all ears.

I've attached the doc for reference. Macros are enabled to allow email address to be pulled from a mailto: hyperlink

View attachment PracticeFinal.xlsm