Vlookup if formula

[gazmoz17]

Hi,

I have a workbook with 3 sheets: Control, List prices, Price List.

The control sheet is a list of inv item lines with a sales price column.

On my Control I wish to to have a column that identifies the type of price present on an invoice item line:

1. PL(Price ref simply present on Price List work sheet) ;these are custom prices per customer.
2. SAGE(price matches lookup on List price work sheet) ; standard list prices.
3. Bespoke (the above 1 & 2 don't apply) so the inv item price is random/custom.

=IF(VLOOKUP(@F3,'List Prices'!A:C,3,FALSE)-Control!J3=0,"SAGE",IF(ISNA(VLOOKUP(D3,'Price List'!H:H,1,FALSE)),"BESPOKE","PL"))

Seems to work other than when both 1. & 2. apply I wish to return 1. "PL" & not "SAGE".

This scenario is when customer has a price list entry which is just the same as list price.....want to show yet despite this its a custom PL price. I think its the order of my if statement however I'm getting errors when trying to do the Price List argument first.

Any help much appreciated.

 

[gazmoz17]

I've never used the IF(ISNA function. The value if true to my logic should be "PL" & then "BESPOKE" but seems to only work the other way around.

=IF(VLOOKUP(@F3,'List Prices'!A:C,3,FALSE)-Control!J3=0,"SAGE",IF(ISNA(VLOOKUP(D3,'Price List'!H:H,1,FALSE)),"BESPOKE","PL"))