You might also be interested in
Replies (9)
Please login or register to join the discussion.
I often use Vlookup, it's so helpful to me. As soon as i have done a Vlookup, I instantly change the formula to values only, this way there is no issue with adding/removing columns later.
index(match()) achieves the same results as v/hlookup, while allowing you to insert, delete and rearrange columns almost at will. You don't even need the lookup "key" to be to the left of the data you want returned, which gives far more flexibility for error checking.
Another vote for INDEX/MATCH here - can be a bit more difficult to learn if you're not used to it, but once you understand how the logic of the individual functions work they can be so much more flexible and powerful than the H/VLOOKUP functions.
A very good article, however as we have been advised on Accounting Web, spreadsheets are finished, so you have wasted your time ;).
On the upside those that say spreadsheets are finished, but never really explain properly whats going to replace them.
Index match works great but when you have large sets of data being cross referenced in a single workbook, the number of calculations required and size of the workbook can increase at the expense of performance speed.
My way around this is to use combinations of sumifs to narrow down criteria and sometimes dynamic named ranges referenced by array formulas.
The problem with a lot of these methods is that for other users of the workbook it's very difficult to follow through the calculations if they need to pick it apart!
Is there a better way that allows ease of traceability?
Without wishing to pre-empt Liam's follow up article, it sounds like it would be worth investigating the Power Query/Get & Transform tools:
https://www.accountingweb.co.uk/tech/excel/supercharge-excel-part-4-vloo...
https://www.accountingweb.co.uk/tech/excel/supercharge-excel-vlookup-v-m...
As someone who hasnt got further than =(sum....) thank you Liam for a clear understandable article. More please.
And AndrewV12 - may I direct you to this:
https://www.accountingweb.co.uk/tech/accounting-software/mtd-avalara-bri...
Spreadsheets are most definitely here to stay. Which is why HMRC backed down and now permits spreadsheet links under MTDVAT.
When dealing with tabulated data, Tables ('Ctrl_t') (VBA: List Objects) should generally now be employed since you can refer to the table in functions by name and fieldname(s) (column headings). It enforces function consistency per field in the table (if your table contains functions) and maintains formatting when adding additional rows, and fields can be repositioned by dragging and dropping. It also makes reviewing look-up functions far more intuitive. Data itself should be independent of order (both rows and columns) otherwise it implied there is a missing 'orderNo' field. Problems encountered here in the vlookup example of having multiple choices indicate an issue with the data or the solution (as IDs should be unique), alternatively other fields should be employed to create combination keys. At that point using SumIf() of SumIfs() can be used (you could validate the number of values summed with CountIf()). The benefit of Vlookup/Index-Match is that it can return a non numberic values. Personally I prefer using SumProduct() as an multi-conditional boolean array in a Table
eg. =SUMPRODUCT((tbTransport[vehicleType]="Car")*(tbTransport[colour]="Blue")*(tbTransport[value])). The example here is using a Table (tbTransport) of three fields: 'VehicleType', 'Colour', and a numerical field 'value'. Note in all these examples the looked-up field position in the table is irrelevant.
Liam: I'm keen to read your promised follow-up article: ...'So what should modellers use instead?'.
With all due respect, "In fact, they can never be used to return data for columns to the left (VLOOKUP) or rows above (HLOOKUP)" is simply NOT TRUE!!
All too often VLOOKUP and HLOOKUP are accused of deficiencies, but this is usually done in unfair comparison with two other functions used in combination (e.g. INDEX & MATCH) to overcome a deficiency/limitation in the naked function.
VLOOKUP and HLOOKUP can be used to look in the opposite direction (i.e. left or up respectively) by using the CHOOSE function to return the table_array argument, and the integer 2 for the column_index_num. This works in an almost identical way to an IDNEX/MATCH combo - even allowing non-contiguous ranges to be specified for the location of the lookup and return values!
Check out the tutorial on Mynda Treacy's MyOnlineTrainingHub blog: https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose
or search the web where you'll find a number of variations on the same theme.
VLOOKUP and HLOOKUP are NOT as impotent as many espouse!