Reverse Vlookup? Index/Match combo? Unsure how to proceed...

[locolobo]

Hey Everyone,

I'm not certain what to search for, but cannot seem to find the solution whenever I search and can't seem to resolve a problem i'm struggling with.

I have a worksheet that references a second worksheet to tell what the volume/associate is for a particular set of lanes.

I'm trying to get the data on the left hand side in column B to populate from the lane that it associates with in the date on the right hand side.

I've tried index & match, but can't been able to get it to work out. I feel like i'm missing something.

Any/all health is appreciated.

I can't share the actual file, this is very similar to the one i'm working on.

[menem]

I'm a bit confused,

on the right table,
for laneset 4,5,6, how did they got lanes?
for laneset 3 , why did it got 11 ?

and did associate effected to the result ?

regards.

[locolobo]

I've updated the file that's attached, the cells in gray are irrelevant, it's just an idea of what the original file looks like.

The lanes on the right hand side are assigned on another worksheet that is used for daily staffing, these change about once a week and i'm trying to make a dynamic staffing sheet that needs minimal effort to update.

No particular reason for laneset 3 to have lane 11, sometimes this happens due to associate experience.

Thanks for your response!

[Gregb11]

Try this in H3 and copy down:

=TRANSPOSE(UNIQUE(FILTER(C3:C22,B3:B22=F3)))

[menem]

Please try

H3
=IFERROR(INDEX($C$3:$C$22,AGGREGATE(15,6,(ROW($C$3:$C$22)-ROW($C$2))/(1/($B$3:$B$22=$F3)),COLUMNS($G3:G3))),0)

Regards.

[locolobo]

Thank you both for the responses.

So i'm sorry, i must've explained it backwards. I need the data in the H3:L8 section to fill in the C3:C22 section.

[wk9128]

ANS. POST#6

Cell C3 formula , drag down

Please Login or Register  to view this content.

[Gregb11]

Another option
C3 formula copied down:

=IFERROR(LET(a,FILTER($H$3:$L$8,$F$3:$F$8=B3),FILTER(a,SEQUENCE(,COUNTA(a))=COUNTIFS(B2:B$3,B3)+1)),0)

[locolobo]

Thank you everyone for the help, I utilized WK9128's function and it worked. GrebN11, i'm going to try yours also to see what I can use for the future.

I've trolled this site for other references in the past, and i'm glad I finally joined.

Again, thanks everyone for your assistance & input

[wk9128]

@locolobo You're Welcome. Glad to help . Thank You for the feedback and rep.