Results 1 to 3 of 3

Thread: VLOOKUP or Index Match for me?

  1. #1

    Post VLOOKUP or Index Match for me?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Practice.xlsm

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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?


  3. #3
    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

    PracticeFinal.xlsm
    Last edited by huskerjhansen; 2014-01-27 at 05:52 AM. Reason: added doc

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •