MS Excel / VBA help thread

You are reading a single comment by @duncs and its replies. Click here to read the full conversation.

•

moocher in reply to @duncs

thanks - works perfectly

what i dislike about this, and hlookups too for that matter, is that it is not obvious to me what this is doing.

•

duncs in reply to @moocher

Match looks down a list of values and tells you where the thing you're searching for is.

So in the pic, it searches 1, 2, 3 and returns 1 (because 1 happens to be the first item in the array, not because it's the number 1 you're looking for).

Then index returns the nth item in an array.

So you can mimic a normal vlookup with index/match

=VLOOKUP(A1, B:C, 2, FALSE)
=INDEX(C:C, MATCH(A1, B:B))

About

@duncs started