Is it appropriate to use vlookup in this scenario

Question

So I have an Excel table that looks like this:

Name Category Value
Joe  A
Joe  B        
Joe  C        4.2
Joe  D        3.22
Brad A        
Brad B        
Brad C        2.25
Brad D        4.1
Ryan A
Ryan B
Ryan C        1.22
Ryan D        5.22

Now, I want it to look like this:

Name Category Value
Joe  A        4.2
Joe  B        4.2
Joe  C        4.2
Joe  D        3.22
Brad A        2.25
Brad B        2.25
Brad C        2.25
Brad D        4.1
Ryan A        1.22
Ryan B        1.22
Ryan C        1.22
Ryan D        5.22

I was thinking of using an if statement combined with a vlookup (something like =if(isblank([value cell]),vlookup([name and category cell combination], [all columns in table], 3, false),[value cell]), but vlookup doesn't seem to accept multiple criteria. Is there a way to get the syntax to work here, or should I use a different function to do the job?

What is your excel version? – Harun24hr May 15 '23 at 03:33 — Harun24hr, May 15 '23 at 03:33
Check my answers and let us know your feedback. – Harun24hr May 15 '23 at 04:31 — Harun24hr, May 15 '23 at 04:31

score 1 · Answer 1 · answered May 15 '23 at 03:55

1

If you user of Microsoft-365 then could try-

=BYROW(C2:C13,
LAMBDA(x,
LET(tmp,x:INDEX(C2:C13,12),
INDEX(FILTER(tmp,tmp<>""),1)
)))

answered May 15 '23 at 03:55

Harun24hr

30,391
4
21
36

Works for this specific table, but not for really large datasets. Thanks for the help though. – Vuotelin May 15 '23 at 17:02

score 1 · Answer 2 · answered May 15 '23 at 04:31

1

For Excel-2010 try the following array formula-

=INDEX($C$2:$C$13,MIN(IF($C2:$C$13<>"",ROW($C2:$C$13),""))-ROW($C$1))

Array formula means you must enter the formula by pressing CTRL+SHIFT+ENTER.

answered May 15 '23 at 04:31

Harun24hr

30,391
4
21
36

Is it appropriate to use vlookup in this scenario

2 Answers2