Lookup a value based two criteria, but the source is in the same column

Update 15.02.19 12:18: Re-written the whole thread for simplification.

I apologise in advance. This issue can seem complicated. However, I'll try to simplify this as much as possible, without leaving out important criteria.

I work in the quality engineering business, and I'll use the relevant terms here:
Supplier number — This is a number that identifies a specific supplier.
Part number — This is a number that identifies a specific part.
Report number — This is a number that identifies a specific report. (A report always contains a supplier, part, and creation date).

What I want, is to lookup a report creation date and report number based on my criteria: Supplier and Part.
Look at the image below. It's divided into three sections: The left is my source data, the middle is what criteria I have, and what I'm missing, and the right is what the solution should look like.
IQg13Tw.png

Column B contains the common denominator for the grouping logic. One report number contains 1 supplier and 1 or more parts.
What I want to lookup the report date and number using only the SUPPLIER & PART criteria, as you can see in the middle/right section.

Here's the Excel sheet with this sample for experimentation.
https://drive.google.com/file/d/1q2Q...ew?usp=sharing


Additional info:
This problem would be much simpler if the SOURCE SHEET listed the information such that each row has a unique report value, with a supplier and part value. Such as:
Row 1: Report creation date | Report number | Supplier number | Part number
Row 2: 01.01.2018 11529 3026 805425
Row 3: etcetc...

Then I would just use a INDEX(MATCH(1;(supplier array = supplier)*(part array = part)).
However, here the supplier and part information is scattered on the same column over multiple rows.

In L2 =INDEX($A$2:$A$25,MATCH(K2,$D$2:$D$25,)) and copy down

In M2 =INDEX($B$2:$B$25,MATCH(K2,$D$2:$D$25,)) and copy down.

The issue I have with your request is that the Supplier and Part Number are in the same field. For a solution, we (you) will need to separate these into separate fields for the same record. What distinguishes one from the other. ie. Supplier is always five digits in length, Part number has a hyphen. Is this true. If this is the case I can split the field out, but how will one know that a particular supplier should be assigned to a particular part. I do not see any correlation. Can you advise a relationship that we can depend on?

Alan

I have figured out a way to align the data, if my understanding of your sheet is on target. The supplier info applies to the records above it. If this is true, then using Power Query I was able to transform your data into a table that then allowed me to pivot your data and present a table as shown below.

Data Range

	A	B	C	D	E	F
1	NCR Creation Date	Part	30419	3343	3209	3072
2	8/24/2016	1.120-00016	11736
3	8/24/2016	110508-13551	11735
4	8/24/2016	130902-02255
5	8/24/2016	310401-01314	11736
6	8/24/2016	400602-00695	11735
7	8/25/2016	502371			11739
8	8/25/2016	522698		11738
9	8/25/2016	522700		11738
10	8/26/2016	527860				11741
11	8/26/2016	528645				11741
12	8/26/2016	528646				11741
13	8/26/2016	528700				11741
14	8/26/2016	528979				11741
15	8/26/2016	528980				11741
16	8/26/2016	528981				11741
17	8/26/2016	528982				11741

I did this with the following Mcode in Power Query

this renamed your table as Table1

Please Login or Register  to view this content.

File is attached for your review

An alternative presentation

v	F	G	H
2	NCR Creation Date	Supplier	Part
3	8/24/2016
4		30419
5			1.120-00016
6			110508-13551
7			130902-02255
8			310401-01314
9			400602-00695
10	8/25/2016
11		3209
12			502371
13		3343
14			522698
15			522700
16	8/26/2016
17		3072
18			527860
19			528645
20			528646
21			528700
22			528979
23			528980
24			528981
25			528982

Here is the file once again with a new Pivot Table. You can manipulate it as needed. I have changed it as you have requested. I have added a slicer also that allows you to filter Suppliers.

Yes. I understand. I am able to get the file in an order that allows for the lookups by having the Supplier on the same line as the product but I am unable to perform a look up as you requested. My VBA crashes my system. Hopefully, some one else will be able to make it work.

Will the two associated rows on the NCR Check worksheet always be next to each other?

Using Power Query again, I can get the Vendor and the Part Number and the Report Number all on the same line. Here is the MCode and the file. However, when I apply Index/Match to the sheet, I get an N/A. Hopefully someone here can fix that

Mcode

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

Because this thread already has several responses I will post the link but please remember this for future threads.

https://www.mrexcel.com/forum/excel-...-same-row.html

Originally Posted by 27POP27

for some reason, I'm not able to edit this original post. Now when I click edit the thread text edit page is all empty.

This is a known bug in our system that happens randomly to some posts. When this happens a Moderator cannot edit your post either.

You do not have to edit your post to mark it solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..." (I see your post is already SOLVED but this is for next time.)