INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directional lookup, adding an additional level of power to the formula.
How to use it:-
=INDEX([Index Array], [Optional Row to return], [Optional - Col to return])
=MATCH([Lookup value], [Lookup Array], 0)
(For the purposes of this article I am omitting the Area argument from index and using EXACT match type)
Index array - The range of values you want to return information from.
Row to return - Row number, within the array, to return the value from. If omitted row 1 will be used.
Col to return - Optional. The column, within the array, to return the value from. If omitted column 1 will be used.
Lookup Value - Value to match against.
Lookup Array - Range of values to match against
You use the MATCH formula to get your row, column or both. This will then get the appropriate data from the INDEX array.
I have attached an example spreadsheet. If you change the values in "V Value" and "H Value" you will see how it affects the result.
Why to use it:-
Because you don't have to sort the data within the range you want to search it is very useful for data that will grow dynamically. It also allow you to treat data as a true array within a single formula. So you can perform bi-directional lookups and store data points within different data ranges but with the same reference.
This could be extremely powerful if you need to store references to external source of data, for example. You have a table with the columns "Customer", "External Ref 1" & "External Ref 2". You can then have a summary sheet where the user can input the customer and without having to go searching and without the need for macros you can give the relevant external information.
Also because of this is two separate formula if you need to return several data elements the MATCH can be put into a separate cell. This reduces the number of calculation required when compaired to using V or HLOOKUP
How to use it:-
=INDEX([Index Array], [Optional Row to return], [Optional - Col to return])
=MATCH([Lookup value], [Lookup Array], 0)
(For the purposes of this article I am omitting the Area argument from index and using EXACT match type)
Index array - The range of values you want to return information from.
Row to return - Row number, within the array, to return the value from. If omitted row 1 will be used.
Col to return - Optional. The column, within the array, to return the value from. If omitted column 1 will be used.
Lookup Value - Value to match against.
Lookup Array - Range of values to match against
You use the MATCH formula to get your row, column or both. This will then get the appropriate data from the INDEX array.
I have attached an example spreadsheet. If you change the values in "V Value" and "H Value" you will see how it affects the result.
Why to use it:-
Because you don't have to sort the data within the range you want to search it is very useful for data that will grow dynamically. It also allow you to treat data as a true array within a single formula. So you can perform bi-directional lookups and store data points within different data ranges but with the same reference.
This could be extremely powerful if you need to store references to external source of data, for example. You have a table with the columns "Customer", "External Ref 1" & "External Ref 2". You can then have a summary sheet where the user can input the customer and without having to go searching and without the need for macros you can give the relevant external information.
Also because of this is two separate formula if you need to return several data elements the MATCH can be put into a separate cell. This reduces the number of calculation required when compaired to using V or HLOOKUP