Query related to HLOOKUP or INDEX with MATCH function

Super Contributor

Hello Everyone, 


I have a data, which i want match data to Commission $ column. 


Can we use HLOOKUP or INDEX with MATCH function(both way) to making more dynamic ?


Please help.. 


Here is a attached file..

2 Replies

Based on your file you can use INDEX/MATCH like-



HLOOKUP is a legacy function but then, you may well be using an out of date version of Excel.  It works perfectly well in this context but, in general INDEX/MATCH is more versatile.  In 365 one would use XLOOKUP that replaces HLOOKUP, VLOOKUP and most instances of INDEX/MATCH or LOOKUP.

If you are used the 2007 version of Excel or later i would recommend placing all data within Tables in order to ensure the calculation extends as data is added.