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

1. ## 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.

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.

2. ## Re: LOOKUP a value based on two criteria on different rows but same column

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.

3. ## Re: LOOKUP a value based on two criteria on different rows but same column

Originally Posted by alansidman
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.
Hi Alan,

I should have further explained:
One part number can have multiple suppliers, and multiple report numbers. Thus it's important that the part and supplier number matches for each J and K row.
Also, the same supplier and part can also have multiple reports, and I want to list the date of the most recent report. Thus supplier+part has to match the same report number and date.

This would complicate this matter further I'm afraid

4. ## Re: LOOKUP a value based on two criteria on different rows but same column

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

5. ## Re: LOOKUP a value based on two criteria on different rows but same column

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

6. ## Re: LOOKUP a value based on two criteria on different rows but same column

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

7. ## Re: LOOKUP a value based on two criteria on different rows but same column

Originally Posted by alansidman
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
Hi again,

A supplier number can be 4 or 5 digits, and the part number can be 8, 15 or even text. Thus, the most reliable way to identify a part from supplier if by it's neighboring column always to the left.
And yes, the all part numbers above a supplier number are co-related. However, the best defining characteristic of all values that are in a shared group is by the report number.

Your "Alternate representation" seems to follow the logic of what I refer to. However, I'd advise using the "Report no." instead of the NCR creation date as a group identifier. Since multiple reports can have the same date.

8. ## Re: LOOKUP a value based on two criteria on different rows but same column

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.

9. ## Re: LOOKUP a value based on two criteria on different rows but same column

Originally Posted by alansidman
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.
Hi. I'm so sorry but there seems to be a misunderstanding.
I'm not after a pivot table. I'm after being able to lookup only the report date and report number based on the supplier+part (+ latest report) criteria.
(In this workbook the NCR number = Non-conformance report number).

I've attached the actual workbook I'll be using. I've deleted/hidden all unnecessary info.
What I want here, is to simply fill out the U and V columns in the "Inspection List" sheet.

Does this make more sence?

10. ## Re: LOOKUP a value based on two criteria on different rows but same column

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.

11. ## Re: Lookup a value based two criteria, but the source is in the same column

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

12. ## Re: Lookup a value based two criteria, but the source is in the same column

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.``

13. ## Re: Lookup a value based two criteria, but the source is in the same column

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.

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

14. ## Re: Lookup a value based two criteria, but the source is in the same column

Originally Posted by 6StringJazzer

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.

https://www.mrexcel.com/forum/excel-...-same-row.html
My apologies but this thread seemed to die off (combined with that my original version of this thread was up to misunderstandings), thus I created one there. I'll refer cross-threads at once in the future.
(Also, 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. If I'd save this I'd delete the text/pics/attachments in the post.)

I've received a solution on the other cross-thread, see this: (By Marcelo Branco): https://www.mrexcel.com/forum/excel-...-same-row.html

If a admin is able, please edit my original post to "SOLVED" with a link to this solution.

15. ## Re: Lookup a value based two criteria, but the source is in the same column

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.)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1