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.


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!


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!



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.

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


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