Vlookup Vs Index Match Formula differences

AnnThompson

I have used a few sheets using the Vlookup formula and one using the index match formula for the same lookup, it is populating a column on in a sheet using the data in another spreadsheet. What is the difference? I have always used the reference formula, then recently a SS Help Desk person used the index match and I failed to ask him what the difference is. When would 'Reference' formula be used?

Andrée Starå

Hi @AnnThompson

I hope you're well and safe!

Here are the most significant differences.

• INDEX/MATCH is more secure because you can't change the column to be shown as you can in a VLOOKUP range.
• INDEX/MATCH is also structurally better because moving columns around won't break. VLOOKUP could show something completely different.
• If you're referencing a lot of columns, the data is not sensitive, and it will not move around, I recommend the VLOOKUP. In any other scenario, I recommend INDEX/MATCH.

Here's more info.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @AnnThompson

I hope you're well and safe!

Here are the most significant differences.

• INDEX/MATCH is more secure because you can't change the column to be shown as you can in a VLOOKUP range.
• INDEX/MATCH is also structurally better because moving columns around won't break. VLOOKUP could show something completely different.
• If you're referencing a lot of columns, the data is not sensitive, and it will not move around, I recommend the VLOOKUP. In any other scenario, I recommend INDEX/MATCH.

Here's more info.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

AnnThompson

Super helpful!! Thank you!!

Andrée Starå

@AnnThompson

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!