Jul 17 2022 11:28 PM
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..
Jul 17 2022 11:44 PM - edited Jul 17 2022 11:45 PM
Based on your file you can use INDEX/MATCH like-
=INDEX($J$5:$Q$5,MATCH(B2,$J$4:$Q$4,0))
Jul 17 2022 11:51 PM
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.