Search multiple criteria for item

[Nanaia]

I'm not sure how to word it, but here goes...
I have two spreadsheets (1 old & 1 new) I need to compare specific criteria. I need to search the old CSV of parts and compare to the new CSV of parts and tell me if we have old parts that match the dimensions of the new parts. The CSV headers in each are identical, but the part numbers have changed. I'm trying to find an easy way to have Excel tell me what the old part number is as a cross reference for the new part number. e.g. If new part 101 is 2x2x1 and old part numbers 609 and 845 are 2x2x1 then Excel list 609 and 845 as the result.

I used COUNTIFS to have it tell me how many match. I'm looking for a way to have it tell me what the associated old part numbers for those matches are. I know there's a way, but I haven't figured out how.

 

[Nanaia]

Thank you for your help.

 

[Nanaia]

Finally having more time to work on this, the formula [=IF(COLUMN()-8>$F2,"",AGGREGATE(15,6,ROW(OLD_CSV_Multiple!$BJ:$BJ)/(OLD_CSV_Multiple!$BJ:$BJ=$G2),COLUMN()-8))] used on the COMPARISON sheet gives a #NUM! error even if there are multiple matching rows on the old sheet. The link listed in a previous message is still valid.

 

[Toadstool]

Did you remember on OLD_CSV_Multiple to put into BJ2 the formula
=Q2&"x"&R2&"x"&K2&"x"&L2&"x"&J2
...and copy it down for every row of data?

 

[Nanaia]

Affirmative. I used your formula. To me it looks like it does the same thing as TEXTJOIN. Your formula is used in both OLD column BJ and COMPARISON column G. It's easy to see which should show a result because of the formula used in column F displaying how may old mark numbers match the new criteria.

 

[Toadstool]

It should be COMPARISON colum H which has
=A2&"x"&B2&"x"&D2&"x"&E2&"x"&C2
because you had a value in column G row 280.

 

[Nanaia]

I don't know what you're talking about. COMPARISON G280 has your formula in it. Your suggestion is not working.

 

[Toadstool]

Try this file

Drive

 

[Nanaia]

Thank you. It works now.

I've added columns to the right of your formulae having it look at the OLD tab in the row your formula indicates. Do you know if there's a formula that can look on the OLD tab at the indicated row to display the number in that cell? All of the mark numbers on the OLD tab are in column I. I've been typing [=OLD!I] and whatever row your formula indicates. There are a lot of places to type that in. I put the letter I in cell BG2 and tried using TEXTJOIN to eliminate all the extra typing, but Excel doesn't like it. Is there a different method that would work?

 

[Toadstool]

Row 17 is the first with any hits and the COUNTIF shows 7 occurrences, so you should copy the formulae in I17
=IF(OR(COLUMN()-8>$F17,$F17=0),"",AGGREGATE(15,6,ROW(OLD_CSV_Multiple!$BJ:$BJ)/(OLD_CSV_Multiple!$BJ:$BJ=$H17),COLUMN()-8))
6 more cells to the right to get all 7 row numbers.

If you want to see the actual values then INDEX into the apprpriate column using the row returned by the AGGREGATE. e.g.
=IF(OR(COLUMN()-8>$F17,$F17=0),"",INDEX(OLD_CSV_Multiple!$A:$A,AGGREGATE(15,6,ROW(OLD_CSV_Multiple!$BJ:$BJ)/(OLD_CSV_Multiple!$BJ:$BJ=$H17),COLUMN()-8)))
gives the OPF ID of 151789