Changing VLookups to Index Match using a Referenced Sheet
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
-
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 rangeSince 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!!
-
why when I try this
=INDEX({Checklist ReturnValueRange}, MATCH([site #]@row, {Checklist site#},0))
i get a #notmatch even though my value exists ??????????
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!