Match/Look-up Help

abdool3002

New Member
Joined
May 31, 2015
Messages
2
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.


Capture.jpg
 

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
Joined
Mar 10, 2004
Messages
21,006
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
21,006
Office Version
  1. 365
Platform
  1. Windows
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!
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top