match/index fomula returning duplicate values.

[maximus0120]

hi guys, having trouble with a match/index formula. Im trying to return an employee name based on two criteria. The problem i'm having is that it is returning the same employee name multiple times. Not sure if im explaining myself right so im uploading an example workbook i made so as to not upload sensitive data.

[FDibbins]

its returning the same value because all of your formulas refer to the same date and type

I created a modified version of what you have, but its still refering to the same date/type. i added a helper column on sheet2, columnD cna concatenated the date/time...=B1&C1 copied down, and then on sheet1 used a slightly simpler formula...

=INDEX(data!$A$1:$D$30,MATCH($E$1&$E$8,data!$D$1:$D$30,0),1)

if your real data will have different date/time values, change the references ($E$1&$D$8) as needed

[CheshireCat]

This is the formula you have in cell A1 (and copied down to A10):

Please Login or Register  to view this content.

Your two criteria references are $E$1 and $E$8 and you have both frozen as absolute references. The only thing that changes, as you copy down the formula, are the range references where you are shortening each list from the top.

So the question is, what result are you looking for?

---------- Post added at 03:55 PM ---------- Previous post was at 03:53 PM ----------

duplicate post

[maximus0120]

E1 and E8 would vary based on the date a training session was held and the topic of the drill, date would be entered and type would be a drop down. Im looking for the formula to return the employee name to populate a roster.

[maximus0120]

is there an example you could upload, im having trouble with the formula? Thanks

[FDibbins]

as long as that date and type are static the way you have them, the formula will always return the same emplyee. perhaps if you included another column for the date and for the type, and then have your fomula based on that, it would work better for you?