Combining VLOOKUP and IF statements

[khaycock]

I'm trying to combine two sheets using VLOOKUP but struggled as the columns weren't contiguous. I think the best way to do it is using IF to search each value individually. It all gets quite complicated because I am trying to use data from two sheets. One sheet already has calculations on it linking a Dealer Country to a Country Code.

Dealer Country Country Code Part Number Standard Discount
United Kingdom C_UK1 063286 ??
United Kingdom C_UK1 068614
United Kingdom C_UK1 062293
United Kingdom C_UK1 RHR33002
United Kingdom C_UK1 LMAC585
United Kingdom C_UK1 LMAC383
Saudi Arabia C_SF5 LMAC466
United Kingdom C_UK1 LMAC401
United Kingdom C_UK1 LGNN340
United Kingdom C_UK1 LMAC404-L
United Kingdom C_UK1 LMAC404-R
Italy C_GC2 LMAC404-C
United Kingdom C_UK1 LMAC383
United Kingdom C_UK1 LMAC497
United Kingdom C_UK1 LGEA340
United Kingdom C_UK1 LMAC348-L
Portugal C_GC1 LMAC348-R
United Kingdom C_UK1 LMAC348-C
United Kingdom C_UK1 LMAC383
United Kingdom C_UK1 LMAC497

This country code needs to link to another sheet which determines the standard discount given to that particular part in that country from another sheet that looks like this:

PART_NBR_Rev UK1 GC1 GC2 HL1 HL2 HL3 HL4 HL5 HL6 SF1 SF2 SF3 SF4 SF5 RP1 RP2 APAC SAM
062801 25% 18% 18% 15% 15% 15% 15% 15% 15% 17% 20% 18% 0% 18% 20% 0% 20% 20%
062803 25% 18% 18% 15% 15% 15% 15% 15% 15% 17% 20% 18% 0% 18% 20% 0% 20% 20%
062852 25% 18% 18% 15% 15% 15% 15% 15% 15% 17% 20% 18% 0% 18% 20% 0% 20% 20%
USAD004 36% 33% 33% 25% 25% 23% 23% 20% 25% 33% 23% 30% 0% 33% 20% 20%
LMAC421 36% 33% 33% 25% 25% 23% 23% 20% 25% 33% 23% 30% 0% 33% 20% 20%
062836 36% 33% 33% 25% 25% 23% 23% 20% 25% 33% 23% 30% 0% 33% 20% 20%

So the standard discount is dependant on the part number and country code and this needs to be transferred back to the other sheet.

Any ideas would be very much appreciated!

 

[wideboydixon]

Assuming your sheet starts at A1 then the first discount for lookup is in D2. Also assuming your discount sheet is called Discounts then this formula should work:

=OFFSET(Discounts!$A$1,MATCH($C2,Discounts!$A$1:$A$7,0),MATCH(MID($B2,3,LEN($B2)),Discounts!$A$1:$S$1,0))

Change the name of the other sheet as necessary.

WBD

 

[khaycock]

Assuming your sheet starts at A1 then the first discount for lookup is in D2. Also assuming your discount sheet is called Discounts then this formula should work:

=OFFSET(Discounts!$A$1,MATCH($C2,Discounts!$A$1:$A$7,0),MATCH(MID($B2,3,LEN($B2)),Discounts!$A$1:$S$1,0))

Change the name of the other sheet as necessary.

WBD

I am very new to Excel so do you mind explaining what exactly you did so I can try and amend it to fit in with my data? I have only included a little snippet of it on here

 

[wideboydixon]

MATCH($C2,Discounts!$A$1:$A$7,0) - $C2 is the lookup value (i.e. the product) so this finds the row on which the product appears in the discount page. Change $A$1:$A$7 to match the full range of products on the discount page.

MATCH(MID($B2,3,LEN($B2)),Discounts!$A$1:$S$1,0) - the lookup value is the country code (in B2) without the "C_" at the front. This time we're looking for the column in which this country code appears on the discount page. Change $A$1:$S$1 to match list of country codes on the discounts page.

Discounts!$A$1 - this is the top left cell of the discounts table that contains the "PART_NBR_Rev" text

OFFSET() starts at a cell reference and moves the specific number of rows and columns.

The whole formula is therefore doing the same as starting with your finger at the top left of the table, moving down the rows until you find the part number and then moving across until you're in the right column for the country.

Hope that helps. It's hard to do with without proper cell references, sheet names etc.

WBD