# Match/Look-up Help

#### abdool3002

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Domenic

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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...

Code:
``````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!

Replies
3
Views
449
Replies
16
Views
703
Replies
6
Views
690
Replies
3
Views
573
Replies
1
Views
393

1,196,019
Messages
6,012,901
Members
441,739
Latest member
Jeezer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back