This is something that I've been meaning to get down for some time now and whilst VLookups are easy to remember and knock out repeatedly, I've hopefully had to google for and re-learn Offset Match and Index Match formulae for the last, final time.
Why would I want these?
These formulae are useful for pulling out a single value in a table of data based on one or many criteria. You'd think that isn't a particularly impressive feat but add a few of these to a spreadsheet and you can do some really useful things with more bulk data. If we take the following table as an example and assume that it starts in cell A1 of a spreadsheet all of the examples that follow will refer to it.
| A | B | C | D | E | F | G | |
| 1 | ISBN | Ref | Author | Title | Published | Publisher | Genre |
| 2 | 978-3-16-148410-0 | BJTRD84 | Ben James | The Red Envelope | 05/04/1984 | Random House | row2-column2 |
| 3 | 978-0-9767736-6-5 | ||||||
| 4 | 978-3-16-148410-0 | ||||||
| 5 | 978-1-56619-909-4 | ||||||
| 6 | 978-1-4028-9462-6 | ||||||
| 7 | 978-1-86197-876-9 |
VLookup
The simplest and most humble of the lookup functions vlookup is great at finding a single value given a criteria. If we've got the above data and we want to make some sense of it (let's assume that there's more then 6 rows) we might want to get the title of a book based on it's ISBN number and VLookup is great for this as we're looking for a row based on a value that's unique so we're only ever going to get one row back as a result and we can grab anything on the table.
VLookup(LookupValue, TableArray, ColIndexNo, MatchType) LookupValue: A value to identify our row with or a cell reference. TableArray: A table reference within the spreadsheet to look at. Our identifier MUST be the leftmost column. ColIndexNo: The column index for the data that you want to return. Starting at 1 for the lookup value itself. MatchType: False for an exact match, True for an approximate match (?!)
This should do the job nicely
=VLookup("", A1:G7, 4, True)
Index Offset
If we take out library analogy a little further, let's say that to simplify things they're going to move to a system that's run by their own internal book references rather than the full ISBN's as they're a little unwieldy for the average library user. The library do, however, realise that users may want to find the ISBN for a book by the reference so have asked for this using the table above. Ah! sadly a VLookup isn't going to work here as you can't refer to anything to the left of the lookup column
;){kind=link}
;){kind=link}
;){kind=link}
;){kind=link}