Iferror with multiple vlookups

[SanchoPanza1]

If you look at the attachment, there are 3 tabs (Active Contracts, Monthly Sales by PLI and US_CA Price List).

I am looking for a formula for I2, L2 and O2 on the Active Contracts tab.

Right now I have the following in I2 -> =IFERROR(VLOOKUP(C2,'Monthly Sales by PLI'!N:O,2,0),0%)

Basically giving me the average discount for a specific Price List (off Monthly Sales by PLI) by product group.

I realize that this average is not always the best representation....especially when there is no sales activity.

So, I want a formula that will find the average in Monthly Sales by PLI....and should that average discount be a 0...then I want to VLOOKUP to the US_CA Price List to see if there is a match such as -> =IF(E2='Canada",IFERROR(VLOOKUP(102320&$C2,'US_CA 2016 Price List'!Y:AH,10,0),0 ),IFERROR(VLOOKUP(102320&$C2,'US_CA 2016 Price List'!N:W,10,0),0))

So 1st check Monthly Sales by PLI, if 0% discount, then check US_CA Price List.

http://www.excelforum.com/newthread....=newthread&f=4

[etaf]

Monthly Sales by PLI'!N:O
theres no data in N to O on that sheet

you are looking up the price list ID from sheet "active"
then using the value found in C2 "Price List ID" to then lookup an average figure from the "Monthly Sales by PLI" sheet

in Column A you have a heading
"Average of Discount" - which has the same iD as "Price List ID" is that the column you want to use to lookup and return another value from ?
if so which row should be used ?

in the US_CA Price List
you have a header in column B PRICE_LIST_ID and also in M
which do you want to use

because your example for a vlookup has
US_CA 2016 Price List'!Y:AH
and again there is no data in Y:AH

you can setup an IF to lookup if there is novalue or if its zero using an OR ( ISERROR(vlookup), vlookup = 0)
and if TRUE , then you can lookup the CA value

IF( OR ( ISERROR(vlookup-Monthly Sales by PLI), vlookup-Monthly Sales by PLI = 0), VLOOKUP-US_CA 2016 Price List, vlookup-Monthly Sales by PLI) )

what happens if CA is also a zero ?

[SanchoPanza1]

Monthly Sales by PLI'!N:O
theres no data in N to O on that sheet -> My apologies. I shifted data around. Main concern on this sheet are columns A thru D.

you are looking up the price list ID from sheet "active"
then using the value found in C2 "Price List ID" to then lookup an average figure from the "Monthly Sales by PLI" sheet ->Yes that is correct. This is the 1st vlookup. If there is a zero value on the search then we need to vlookup on the "US_CA 2016 Price List"

in Column A you have a heading
"Average of Discount" - which has the same iD as "Price List ID" is that the column you want to use to lookup and return another value from ?
if so which row should be used ?-> Correct. It will all depend on the product we are looking for an average discount on. I2 we will search in column B in "Monthly Sales by PLI". L2 we will search in column D in "Monthly Sales by PLI". And, O2 we will search in column C in "Monthly Sales by PLI". All pertaining to Price List ID.

in the US_CA Price List
you have a header in column B PRICE_LIST_ID and also in M
which do you want to use -> This is my second VLOOKUP Search parameter. If we find a "0" for that 1st search on the "Monthly Sales by PLI" & E2 on the "Active" sheet reads as Canada....then we want to search columns M thru U on "US_CA Price List". If we find a "0" for that 1st search on the "Monthly Sales by PLI" & anything other then Canada on E2 on "active...then columns B thru J on "US_CA Price List".

because your example for a vlookup has
US_CA 2016 Price List'!Y:AH
and again there is no data in Y:AH -> My apologies. I deleted data for security purposes. Se my comments above.

I essentially want to do as follows (example):
Step 1: =IFERROR(VLOOKUP(I14,'Monthly Sales by PLI'!N:O,2,0),0%)
If above equals 0%,
Step 2: =IF(M2="Canada",IFERROR(VLOOKUP(102320&$I2,'CA Price List Discount'!$A:$J,10,0),0),IFERROR(VLOOKUP(102320&$I2,'US Price List Discount'!$A:$J,10,0),0))
Note: numbers 102320 & 102320 serve merely as identifier (internal use). For purposes of simplicity, I would use Price List ID as the match for VLOOKUP purposes

[etaf]

can you attach another example with the correct layout and data
and range and ID - its very confusing otherwise ..

But I will try to work something out

in I2
VLOOKUP(I14,'Monthly Sales by PLI'!A:B,2,0)
If this returns a zero then we use canada
What if the lookup returns an error - because the Id cannot be found ?
IF( OR ( iserror(VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)=0),
so if the result is a zero or an error use the canada sheet

IF( OR ( iserror(VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)=0), VLOOKUP(c2,'CA Price List Discount'!$A:$J,10,0), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0))
if canada pruduces an error then we need to allow for that and set to zero
IFERROR ( IF( OR ( iserror(VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)=0), VLOOKUP(c2,'CA Price List Discount'!$A:$J,10,0), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), 0)

we now need to add the canada value

Again a little confused when to apply this -


The ranges are all incorrect
if you setout a sample with the correct ranges and data I can help further
and perhaps the order of the rules and ranges


you say

This is my second VLOOKUP Search parameter. If we find a "0" for that 1st search on the "Monthly Sales by PLI" & E2 on the "Active" sheet reads as Canada....then we want to search columns M thru U on "US_CA Price List". If we find a "0" for that 1st search on the "Monthly Sales by PLI" & anything other then Canada on E2 on "active...then columns B thru J on "US_CA Price List".

IFERROR ( IF( OR ( iserror(VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)=0), VLOOKUP(c2,'CA Price List Discount'!$A:$J,10,0), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), 0)

AND( OR ( iserror(VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)), VLOOKUP(C2,'Monthly Sales by PLI'!A:B,2,0)=0), E2="Canada")

[SanchoPanza1]

Thank you for your patience. I have attached a new excel with the correct layout, range, notes, etc.

[etaf]

Thanks unfortunately the pc I have with me has broken so probably be weekend before I pickup my w7 pc sorry about that
The spare one I have does not have office

[etaf]

why are you looking in A/B on Monthly Sales by PLI
if they are always going to be blank/zero
and so look up a different sheet

Need to find a formula here that searches in "Monthly Sales by PLI" sheet by Price List ID (A2=> 354133)
VLOOKUP will only include columns A thru B on "Monthly Sales by PLI" sheet as we are only concerned with Alloderm product average discount
If you notice on the "Monthly Sales by PLI" sheet, there is no discount for this product line
Because there is a blank or "0" for this particular Price List ID (354133 on Alloderm/AD), I now want to do an additional check on the "US_CA 2016 Price List" sheet
On this additional VLOOKUP, I want to check to see if "Canada" appears in D2
In this case it does not, so we would VLOOKUP to columns A thru J on the "US_CA 2016 Price List" sheet

[SanchoPanza1]

why are you looking in A/B on Monthly Sales by PLI
if they are always going to be blank/zero
and so look up a different sheet

That will not always be the case. The revised excel attachment only has a sample of the price lists that I am searching for. In most cases, there will be an average discount on the Monthly Sales by PLI.
I would like to arrive at a full proof check to get rid of all zero values.

[SanchoPanza1]

I have attached another version of the excel with more data & some Q&A section.
Please note that this excel is a sample of a bigger data set.
Your help is very much appreciated.

[etaf]

Ignorring any lookup errors on the sheet and #N/A being returned
this should work
=IF(VLOOKUP(A2,'Monthly Sales by PLI'!A:B,2,FALSE)=0,IF(C2="canada",VLOOKUP(A2,'US_CA 2016 Price List'!M:U,9,FALSE),VLOOKUP(A2,'US_CA 2016 Price List'!B:J,9,FALSE)),VLOOKUP(A2,'Monthly Sales by PLI'!A:B,2,FALSE))

Can you just check that the formula works as you expect - BUT ignore any errors returned - we can correct that later

just want to make sure the basic formula does actually work

if it does - we can add error correction

[SanchoPanza1]

The formula does get closer to a finished product, but as you mention there are errors.

[etaf]

i would edit and remove your email address - thats the quickest way to get spam

if its correct , ignoring errors , then we can setup an error correction

if step1 lookup on PL sheet produces and error then we can use the Canada/non Canada range -
But if that produces an error - what result do you want ?

[etaf]

i have had to use a helper column for the vlookups to see if ther is an error
iserror()
and then test for true OR 0 on the first sheet before going off to canada