Cell formula based on finding an index on another sheet

I have a numbers file with a first sheet like this:

Then a second sheet that looks like this:

For each row in sheet 1 I need to find if there is a matching SKU on sheet 2 (sheet 1, col N with sheet 2, col B). Then, if there is a match, get Mark down value (Sheet 2, col D) and apply that discount on Sheet 1, col T.

I've been trying with this approach here...

...but didn't get there yet.

Is this possible in Mac Numbers? If possible, what would be the way to achieve it?

Sheets don't have the same records nor rows in the same order so I can't just copy and paste a column to use a formula with relative cell positions.

Solution 1:

Your use of match() is relevant but index() is better suited than address() and together they make for a flexible lookup tool. Essentially, match() finds the row with your SKU while index() fetches the corresponding discount. We also can use if() and iserror() to handle unmatched SKUs. Your images don't lend themselves to solving this concisely so I'll use a smaller layout (plus I'm not really sure what should go where).

This formula should go in cell C5 of table 1:

IF(ISERROR(MATCH(A5,SKU,0)),0,INDEX(Table 2::C$1:C$7,MATCH(A5,SKU,0),1,area-index))

Then, in cell D5, put this:

Price CL8016 SG OPT 01−(Price CL8016 SG OPT 01×MD CL8016 SG OPT 01)

An easier rendering would be: D5 = B5 - (B5 * C5)

What they do:

C5 first checks to see if there is a matching SKU in table 2 with MATCH(A5,SKU,0). If not it generates an error, if yes it generates a table 2 row number. The if() function takes the results of iserror() then returns a 0 discount if an error or (via index function) the corresponding discount from table 2 column C.

D5 subtracts the discount amount from the price.

E5 shows how iserror() works with ISERROR(MATCH(A5,SKU,0)).

NB, I managed to upload the bleeping image

Solution 2:

So match() will do this.

MATCH() has three arguments: Search_for : the sku you want to check from col N sheet 1 Search_where : column B on sheet 2 type of match : need 0 for exact match

Not built an example as your data is in images.