Help with If then command in vlookup

[tonkaexcel]

Hi

I hope someone can help. I'm going in circles trying to do a vlookup with if then else commands but no matter how logical i think i'm being it's a mess and not working.

On the enclosed spreadsheet tab "PART NO DISCOUNTS" the "sell price" column (would be "L") draws through pricing based on group and types from the other tabs. Most notably I'm looking at customer code "E15".

We look at discount groups first, then types to obtain a discount. I think we ought to look for special prices based on pricing associated with the stock code first, then group, then type (ie: if there isn't a stock code specific special price or discount then look for group or type).

I hope that makes some sense. All the data in the sheet might be complicating things... goodness knows I'm confusing myself.

I hope someone can help..

[HansV]

What is the exact problem you're having with the formula?
If it returns an incorrect value, can you tell us how the sell price in L295 should be calculated?

[tonkaexcel]

Hi Hans

At the moment it's derived from a long drawn out formula working out the discount from the "Part No Discounts" tab. This issue is J295 brings in from D42 and not D43 as it fails to differentiate between items with a derived discount from Group, type or something specific to the stock code.

My failures give no error but when i get exasperated I put in L295 this:

=if(vlookup([@[Stock Code]],'RAW STOCK DATA'!C:R,13,)else(vlookup([@[concat(pgroup,ptype)]],Table_Query_from_alcatools[[concat(pgroup,ptype)]:[Discount]],3,0)

and variations of the same type of thing.

I'm failing with the logic and the conversion to Excel formula :(

[HansV]

Else is VBA, not an Excel function. But it's not clear to me what you want there. The logical_test part of the IF function is vlookup([@[Stock Code]],'RAW STOCK DATA'!C:R,13,).
However, the stock code E15 doesn't occur in column C of the RAW STOCK DATA sheet; it occurs in columns A and B

[tonkaexcel]

Hi Hans

Many thanks for your help. On sheet "PART NO DISCOUNTS" row 295. I might be over complicating it. In column J that discount is coming in wrong due to it not checking if the data held on "RAW DISCOUNT DATA" has a corresponding stock code. IF it does THEN use the discount in column D, if it doesn't then check column A and use the discount in column D. Would that work or would that also leave it open to error?

[HansV]

Does this do what you want? If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter:

=IFERROR(INDEX('RAW DISCOUNT DATA'!D:D,MATCH(1,('RAW DISCOUNT DATA'!A:A=A295)*('RAW DISCOUNT DATA'!C:C=D295),0)),0)

[tonkaexcel]

Hi Hans

Many thanks, I've tried it but it's not worked. I'd like to use this for that whole column ideally. I've just re-read what I wrote and I'm getting myself in knots, I'm sorry. The validation is on sheet "PART NO DISCOUNTS" column E and correspond with sheet "RAW DISCOUNT DATA" column A.

I'm not sure how to add that formula :(

[HansV]

That would lead to no matches at all. I;'m afraid I don't understand.

[HansV]

Can you try to explain in very small steps what you want the formula to do?

[tonkaexcel]

Hi Hans

I'm sorry, I am making a mess of this. I'll make it as clear as I can.

Objective:
Bring in the correct discount to sheet "PART NO DISCOUNTS" to column J (Discount) from sheet "RAW DISCOUNT DATA" column D (Discount).

The main validator from sheet "PART NO DSCOUNTS" column B (Stock Code) to check against Sheet "RAW DISCOUNT DATA" column G (stcode) then bring in the discount (column D) from this sheet.

If the stock code cannot be found using the stock code as a validator, use sheet "PART NO DISCOUNTS" column E (concat(pgroup.ptype) to lookup the data on sheet "RAW DISCOUNT DATA" column A (concat(pgroup.ptype) and bring in the discount from column D

I hope I've made that sound better, I've read it a few times and I think it makes sense....I hope

[HansV]

Here is my next attempt.

Book1.xlsx

[tonkaexcel]

Thanks Hans.

I really am grateful to you :)

You are the best