Changing VLookups to Index Match using a Referenced Sheet

Sldollman · 04/23/19

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. · 04/23/19

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 · 04/23/19

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 · 04/24/19

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

Sldollman · 04/24/19

@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 · 04/17/20

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å · 04/19/20

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

Changing VLookups to Index Match using a Referenced Sheet

Comments

Help Article Resources

Categories