Match/Look-up Help

[abdool3002]

Hey Guys,

I've been trying to figure out a solution to this all day but I've come up short. I'm pretty sure this will be straight forward for you experts . I've uploaded a snapshot of my sheet below. I have four similarly structured sheets that each include a "REASONS" column and its corresponding records for that reason. I want to create a summary sheet that will include each record number e.g. 7V6VN8, 3D3PS9 and its reason from my four sheets. I'm unsure of how to do this. I need to lookup the record number form the array and return it's reason. Can someone please help. Thank you so much in advance.

 

[Domenic]

Does or can the same record number occur more than once in any one sheet?

Does or can the same record number occur in more than one sheet?

 

[abdool3002]

Does or can the same record number occur more than once in any one sheet?

Does or can the same record number occur in more than one sheet?

Hey Domenic, yes the same record may occur in one or more sheets.

 

[Domenic]

But can the same record occur more than once in any one sheet? In other words, can a record have more than one reason for any one sheet? Assuming that this is the case, I think we'll need to do the summary by sheet. So, for example, for your first sheet, let's say Sheet1, try...

1) On a separate sheet, list your record numbers in Column A, starting at A2.

2) Then try the following formula...

B2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(REPT("z",255),Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$34,SMALL(IF(Sheet1!$E$2:$V$34=$A2,ROW(Sheet1!$A$2:$A$34)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))))))

3) Do the same for your other sheets.

Hope this helps!