Viewing a single comment thread. View all comments

jj-reeder t1_j97w1ug wrote

If you know vlookup alone, you can make a LOT of people believe you’re a genius.

269

textris t1_j98qeoo wrote

INDEX MATCH ftw!

78

Good-Astronomer-1138 t1_j98y572 wrote

This has been replaced by XLOOKUP now

68

tomistruth t1_j99qiki wrote

Example code for people who rarely work in excel?

8

thequicknessinc t1_j9a4cj1 wrote

=XLOOKUP(value to look up,range/array to lookup value in, range/array to return)

16

tomistruth t1_j9a60l0 wrote

Thx! Noted.

5

Good-Astronomer-1138 t1_j9a8hqd wrote

You can also denote a value for blanks and select a search order at the end : so like xlookup(value, search range, return range, “”, 1) would default all blanks to blank and sort in the default way

3

PatrickKieliszek t1_j98xyao wrote

Index Match is good if your data set is large and you're worried about execution time.

For small data I almost exclusively use xlookup.

14

geekgodzeus t1_j99h77s wrote

I recently started using SUMIFS and it has been a godsend .

9

JethroFire t1_j9960df wrote

Fuuck, I came here to say this and you beat me to it. I like Index and Match, too. Also MaxIfs and MinIfs are super cool functions.

4

nucumber t1_j9akyn9 wrote

you can go far with xlookup, which gives some database functionality to a spreadsheet. the problem is you can go too far, trying to manipulate too much data from too many spreadsheets.... all those linked tabs etc

at that point you should learn Access and maybe SQL to handle the mass of data

1