Changing VLookups to Index Match using a Referenced Sheet

Sldollman

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!

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

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

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

 

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!!

Tom Hicke

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å

@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!