Changing VLookups to Index Match using a Referenced Sheet

Sldollman
Sldollman ✭✭✭
edited 12/09/19 in Formulas and Functions

Help! I am completely lost on making an Index Match work for current VLookups, but I believe I need to figure it out to make my sheet more usable.

I have a summary sheet template that has multiple Vlookups to a large master table.  It is getting unruly to try to track the column #s (over 75) with a Vlookup.  Every time someone makes a change to the master table, my summary sheet is off.

Scenario:

Master sheet includes data for all sites.  Summary sheet has data for one specific site.

Summary sheet needs to look up data on master sheet based on site # and report for each column in the master sheet for that specific site.  Summary sheet is a template that is renamed for each site.  Site # (Primary Column in master sheet) is used as the lookup.  I type in the site number (Primary Column) on the summary sheet in row 1 and in the vlookup references the Primary Column in the master sheet.  Make sense? 

Example of current Vlookup in one cell of summary sheet:

=IFERROR(VLOOKUP([Primary Column]1, {Checklist Range 1}, 13, false) + "", "-")

The problem is trying to keep track of the column numbers in the master sheet.  In this case, column 13 in the master sheet is the site name. 

How can I make this work with Index Match?  I don't understand how it uses a specific value for the match (in this example, primary key (site #).  

Please help me wrap my head around this! 

Thank you!

Comments

  • Mike L.
    Mike L. ✭✭✭

    I'm somewhat challenged when I do things like this, as such I have to break down what I'm doing in my mind into small parts. 

    I would suggest making some adjustments first to your vlookup formula.  Since your vlookup formula already works you can make these adjustments to a working formula.  This is just my method. If I try to do too many things at once I just get lost quickly. 

    To the existing vlookup formula. 

    =IFERROR(VLOOKUP([Primary Column]1, {Checklist Range 1}, 13, false) + "", "-")

    1) Name the Primary Column: Right click on the column header and select edit column properties.

    =IFERROR(VLOOKUP([site #]1, {Checklist Range 1}, 13, false) + "", "-")

    2) Use "@row" instead of the actual row number.

    =IFERROR(VLOOKUP([site #]@row, {Checklist Range 1}, 13, false) + "", "-")

    3) Add labels when making cross sheet references.  Your formula contains {Checklist Range 1} .  You can replace "Range 1" with a the name of the column when you create the formula.  I like to do this to just make the formula easier to follow. I also try to remove spaces to eliminate the need for [brackets]. Assuming you're matching the row's site # on the master list in a column also called "site #". 

    =IFERROR(VLOOKUP([site #]@row, {Checklist site#}, 13, false) + "", "-")

    If that works try INDEX match (I'm going to drop the IFERROR, you can add it back in to the formula after you get INDEX/MATCH to work. 

    =INDEX({Checklist ReturnValueRange@}, MATCH([site #]@row, {Checklist site#},0))

    so the basic structure is: 

    return value from this range,

    by matching a value from this row

    with a value in this other range

    Since your return value comes from a defined range and not a column number it won't break if someone adds columns to the master sheet. 

    Hope this helps!

     

     

  • Brian W
    Brian W ✭✭

    I use INDEX/MATCH a lot and I always get confused trying to remember how it works. Basically the formula works like this:

    =INDEX([Column to Return]:[Column to Return], MATCH([Item To Look For]@row, [Column To Search]:[Column To Search], 0))

    So, if you're looking up the Site # in Row 1 of the Primary Column on your Summary Sheet and want to return the name from the Master Sheet, your formula should look something like this:

    =INDEX({Site Name Column on Master Sheet}, MATCH([Primary Column]1, {Site # Column on Master Sheet}, 0))

    The 0 is important and easy to miss. Let me know if this helps.

  • Sldollman
    Sldollman ✭✭✭

    @ Mike L:  Thank you for the tips on cleaning up/simplifying my Vlookups! very helpful.

     

  • Sldollman
    Sldollman ✭✭✭

    @Brian W.  This is the first time I actually understand how Index Match works! Thank you for breaking it down for me.  I was successful in my first attempt on my summary sheet.  Thank you!!

  • why when I try this

    =INDEX({Checklist ReturnValueRange}, MATCH([site #]@row, {Checklist site#},0))

    i get a #notmatch even though my value exists ??????????

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Tom Hicke

    Hi Tom,

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!