oh, vlookup is trending again, is it time for my bi-annual lecture on why you should use index/match instead?

alright so vlookup is great, of course, but it requires a specific data layout - it can only search from left to right, and match in columns (hence the V). So, of course, there's HLOOKUP if you do want to match horizontally, but that's fairly niche.

the key thing is the left-to-right searching means you often need to rearrange datasets, add in duplicate columns or whatever to be able to search properly. INDEX / MATCH doesn't have these limitations, and is a far more robust set of tools that can be applied in many ways

Index is a function that looks like this:
INDEX(range, row number, column number)
So if you feed it a single column, row 1 and column 0, it will return the first value in that column.
If, instead of "1", we enter a formula to search for our lookup value, we get our lookup result

Match looks like:
MATCH(lookup value, range, [match type])

So this is simple, If I have a range with two values:
Ann
Bob

And I search for Ann, it will return 1, whereas Bob will return 2

(You want match type 0, btw)

So now we put them together like this:
INDEX(Results column, MATCH(lookup value, lookup column, 0), 0)
Will search one column for the lookup value and return the same position from the results column.

On its own it can be hard to see the value in switching from VLOOKUP, unless you already hate having to rearrange your columns and etc. But over time you'll find INDEX/MATCH can be used in a variety of different situations where VLOOKUP or HLOOKUP alone wouldn't get the jobdone

And of course, INDEX is at its most powerful when you are also utilising the column number, to reference an entire table instead of just a single column. Once you've played around with this a few times you'll probably get the gist of how that works too

. @MattHancock any jobs goin pal

i know i said you're a clueless bellend who really should have been punched properly in leeds that time but i'll straighten this lot right up