for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

SPR May 2023 12:41pm, 18 May 2023 40,553 posts	Quote Pin SPR A broken clock is right twice a day 😜 Index/ match is firmly embedded in my brain. The thing is, VLOOKUP used to frustrate me before I knew about Index/ match so I was happy to discard it.
GlennR May 2023 12:50pm, 18 May 2023 70,308 posts	Quote Pin GlennR Ironically, I would now have to revise VLOOKUP before I could use it. It’s certainly more than twenty years.
SPR May 2023 12:55pm, 18 May 2023 40,555 posts	Quote Pin SPR I can still recall the syntax.as I end up looking at other people's spreadsheets that have it sometimes. Index/ Match being clear about exactly what column/ row is being returned (explicitly selected Vs a number) is one of the benefits.
larkim May 2023 12:57pm, 18 May 2023 21,130 posts	Quote Pin larkim Hi, my name is larkim and I'm a vlookup addict. It's been 2 day since I last used.
SPR May 2023 12:59pm, 18 May 2023 40,556 posts	Quote Pin SPR If you combine Index/ match with tables, you have the name of the table column being returned so perfect for reviewing.
SPR May 2023 1:03pm, 18 May 2023 40,557 posts	Quote Pin SPR TBF, Xlookup seems to have pretty much all the benefits of Index/ match so maybe all the Vlookup junkies can move straight to that and the Index/ match bunch end up like Liverpool/ Manchester United after their period of dominance, lol.
SPR May 2023 1:03pm, 18 May 2023 40,558 posts	Quote Pin SPR I note pothunter said there was an issue with named ranges though...
larkim May 2023 1:09pm, 18 May 2023 21,131 posts	Quote Pin larkim In my head vlookup is this:- - vlookup(what?,in where?,and which column of data do you want?,false) I just need to get index(match) in the same idea in my head. Is it:- =index(in where?,(match(what?,in which single column range?,0(justlikefalse)),and which column of data do you want?)) Just a tad more complex. And I've used it from time to time where the data isn't in the right order left to right, but I more often just drop in a helper column, and / or wrap vlookup all in an ifna() statement. (Like who would ever want to find approximate matches??!?)
SPR May 2023 1:42pm, 18 May 2023 40,560 posts	Quote Pin SPR That's something the new X formulas seem to solve the re approximate match. Using index in the same way as Vlookup Index(Column with the answer you want, Match(item you're looking for, column you want to check for the item, 0)) 0 = exact match as you know.
SPR May 2023 1:43pm, 18 May 2023 40,561 posts	Quote Pin SPR You can omit the column number if you're using as a VLOOKUP as there's only one column to return.