CODEWITHSUNDEEP
excelexcel-formula
KRS77
KRS77

Reputation: 40

If no match found between two columns do this

I have two columns. I want the formula to pull data from a different sheet and place it into a column that corresponds to the same part number in the first sheet.

Here is what I have so far that's working for me:

=IFERROR(VLOOKUP(B2,master_inventory_list.xlsx!mt_data, 2, FALSE), "No Exact Match Found")

However, in addition to this, if there is no match found, I would like the formula to place the non-matching data from the second sheet and add its entire row to the bottom of the first sheet. Is this possible?

Thank you!

Additional information:

Here is what sheet 1 looks like:

enter image description here

Here is what sheet 2 looks like:

enter image description here

Upvotes: 1

Views: 3303

Answers (2)

M.L
M.L

Reputation: 328

This is a crude solution - works well if you don't have to do repetitive tasks, and if INDEX/MATCH formula are not what you want to play around with due to their clunkiness.

In you sheet2, you can add a column to the left, which matches values of the product no into the Sheet 1 product no column. Formula is =MATCH(E3,$A$3:$A$7,0) in the sample screenshot. When it gives an "#N/A", you can filter them product nos out and copy-paste as values underneath the first sheet.

It is a crude method, but I use it if it needs to be done as a one time activity, as against devising a complicated formula (could take time), or a heavy formula which could make the file heavy.

Does that help?

enter image description here

Upvotes: 1

M.L
M.L

Reputation: 328

If you wish to add it as a formula underneath your Sheet 1, it can be done by nesting INDEX and MATCH function.

Let's say your sheet 1 is:

Sheet1  
    PN  MT-Data
    1   A
    2   B
    3   C
    4   D
    5   E

And sheet 2 is:

Sheet 2 
PN  MT-Data
1   A
3   C
2   B
4   D
8   F
7   G
6   H
5   E

(Have used them in the illustrative pic) So, in sheet 1, under PN = 5, you need to find the missing PN from Sheet 2. The formula to write that cell (A8 in the illustration) is =INDEX($D$3:$D$10,MATCH(0,INDEX(COUNTIF($A$3:A7,$D$3:$D$10),0,0),0)). [You can understand the inner workings of this by using the Evaluate Formula button]

You can drag this formula down and it will keep popping numbers. When the list exhausts, an #N/A will appear. You will ofcourse need your own VLOOKUP formula that you have to be extended in the column next to it.

Is this what you were looking for? (This seems to be processing the sample data properly, hopefully it fits your purpose) enter image description here

Upvotes: 1

Related Questions