index 10 different columns, all with same match criteria

Jeff Reisman

@amschock The fact that you're not getting a #NO MATCH error in those cells tells us that the match part is working.

Tell me, on Sheet B (23MasterCallOut sheet?) is there more than one row for a given employee ID? If there is, an INDEX/COLLECT may be better suited to use here, since you can specify more criteria to narrow down the row you want the data from.

amschock

@Jeff Reisman yes, one entry (row) for each of these columns.

I've never used index/collect. Will go try now.

Paul Newcome

It also looks like you have two different references you are matching on.

{23MasterCallOutEmployeeID}

{23MCO EID}

Friday, March 31:

=INDEX({23MasterCallOutFriday3/31}, MATCH([Employee ID]@row, {23MasterCallOutEmployeeID}, 0))

Saturday, April 1:

=INDEX({23MCO 4/1}, MATCH([Employee ID]@row, {23MasterCallOutEmployeeID}, 0))

Sunday, April 2:

=INDEX({23MasterCallOutSunday4/2}, MATCH([Employee ID]@row, {23MCO EID}, 0))

Monday, April 3:

=INDEX({23MasterCallOutMonday4/3}, MATCH([Employee ID]@row, {23MCO EID}, 0))

amschock

@Paul Newcome I guess that's from last night when I was trying the 'helper column" on Sheet B. It wasn't working when it was all the same reference column either.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361454#Comment_361454

So if the MATCH part could match with more than one row on Sheet B, that would explain why you're getting values in one column but not values in the others despite the match being successful. Look what happens:

Sheet A was previously filled with values in each column for each Employee ID. Once I added some blanks into Sheet B, the formula pulled the blanks because it found those values first.

When I move the complete rows above the incomplete rows, the formula finds the complete rows first:

To use INDEX/COLLECT, identify the other criteria that helps you pick WHICH of the matching rows to use. For example, if you always want the newest rows, you can use the MAX function within the COLLECT portion to indicate you want the row for that employee ID with the newest created date or entry date.

amschock

@Paul Newcome @Jeff Reisman

index/collect for the WIN!

Woohoo!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361485#Comment_361485

YES!!! Fantastic! What other criteria did you end up using to pick the correct matching row?

Pauline J

@Paul Newcome I have been hammering this issue for a whole day! the cell references keep changing every time I edit another one - and now I see that I'm selecting Edit Reference instead of Reference Another Sheet — which of course edits ALL references to that range. Duh … You saved my sanity today! 😊