New favourite things

You could say that we, ahem, look down on vlookups

2 Likes

woof!

1 Like

2 Likes

Also I was told that with vlookup the lookup value has to be in the first column of the table/spreadsheet you’re searching in, right? I’ve never deviated from that myself because that’s what I was told but it does instantly show the limits of a vlookup (if true).

Also don’t forget our friend hlookup. Always getting forgotten about that guy :frowning:

1 Like

My favourite piece of music at the moment is the music that plays over the end credits of season 1 of Dawson’s Creek.

1 Like

:smiley: :smiley:

1 Like

Beautiful, isn’t it? Why they changed it for season 2, I don’t know. I don’t know.

1 Like

I dunno if it has to be in the first column but it can only work left to right, which is a weird limitation. Also it only looks through the columns you’ve selected, not the whole table, so it’s easier on the ol’ processor.

Love a good index match me. The day I figured out how to do a multiple criteria index match array formula was a big day for ol’ horsey.

Go on…

Yeah I might start vibing off index matches from now on, now it seems to be all you lot EVER talk about.

Key question - if the criteria is not met does it return a blank or an #N/A like a vlookup does?

It brings back an #N/A. Unless you bung an ISERROR as well.

Soleros.

Hadn’t really had one, but didn’t think I liked them, until a few months ago. They are the absolute best ice cream though.

Sound. tbf it’s nothing a Copy > Paste as Values then Find/Replace #N/A with " " doesn’t solve for what I use it for. Takes 10 seconds.

It’s dead easy - you just stick an ampersand in between your two criteria and your two search ranges. So if you were looking for something that matched values in both A2 and B2 and you were matching them to a sheet where the data from A2 was to be found in the A column and the data from B2 is in the B column it’d look like this:

{=INDEX(‘Dummy Data’!C:C, MATCH(A2&B2, ‘Dummy Data’!A:A&‘Dummy Data’!B:B, 0))}

Really handy in my job for pulling together exam data where students have sat multiple stations and the data always comes through with each station on a separate line.

1 Like

I love it when a thread starts fun and then Excel Geeks harsh the buzz with spreadsheet chat.

3 Likes

Yeah I do that too tbh. I only put ISERROR in when I’m trying to show off.

1 Like

And show off you should. I’m very much sub-intermediate Excel so I’m not risking fucking everything with extra criteria when I don’t need to. No sir-ee Bob.

Any minute now I’m gonna crash that anecdote thread with a doozy of a story about the day I blew my bosses mind with a ridiculously long improvised nested if formula.

1 Like