Nested If Contains Function Returning #No Match

Garrett.Ricker

Having and issue with a nested =IF(CONTAINS( function returning a result of #NO MATCH. Formulas work independently of each other just not nested. I also confirmed the "search value" within the VLOOKUP is on the lookup table. What am I missing?

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false), IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)))

Ipshita

@Garrett.Ricker sometimes we miss just a paratheses and that ) just ruins the entire formula. First check for such errors, also, always start all your formulas with an =IFERROR it is just good practice as it will check for the errors in your formula. I'm yet to test out your formula but try and look up for these while I test yours.

Cheers,

Ipshita

Garrett.Ricker

Ipshita,

If the formula was missing a ) then it would show as #UNPARSEABLE correct? When converting to an IFERROR statement I get a #INCORRECT ARGUMENT SET error. @Andrée Starå would you be able to provide some insight?

Paul Newcome

It looks like both of your VLOOKUPs are identical. Since that is the case, if it is a no match on the first one, it would also be a no match on the second one. Is that intentional? Can you show the formulas that you used that are working independently?

Garrett.Ricker

Hi @Paul Newcome,

These formulas work and returns the desired result independently. However when I go to nest them, I get the #NO Match when trying to pull results for the 2nd DC Rate Capture Sheet.

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false)) (WORKS)

=IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)) (WORKS)

Nested Formula: Works for the 1st DC target and not the 2nd.

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false), IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)))

Paul Newcome

That means it is not getting a match in the second sheet. Double check that the second sheet does in fact have the matching string in the appropriate range.

Garrett.Ricker

I confirmed that and also explains why the formulas work independently of each other. Issue is that they are not working when nested and I cant figure out why.

Paul Newcome

What happens with this (there should be an error if neither are "ABC" nor "XYZ" are not found, but we can fix that pretty easily)?

=VLOOKUP([Lane Concat w/ Supplier Name]@row, IF(CONTAINS("ABC", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - ABC Range 1}, IF(CONTAINS("XYZ", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - XYZ Range 1}, "")), 26, false)

Garrett.Ricker

=VLOOKUP([Lane Concat w/ Supplier Name]@row, IF(CONTAINS("ABC", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - ABC Range 2}, IF(CONTAINS("XYZ", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - XYZ Range 1},"")),26,false) is still resulting in the same issue. The inital lookup is upulling the correct result however when it transitions to the next DC sheet it shows as no match.

Paul Newcome

Are you able to provide some screenshots of each of the three formulas with each of the four outputs?

Formula 1 alone working

Formula 2 alone working

Formula 3 working

Formula 3 not working

As well as a screenshot of the reference data that shows a row where the one that is not working should be matching up?

Garrett.Ricker

Formula 1 - Pulling in data from DC ABC and XYZ

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false), IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)))

Result = $500 (Correct Result for DC ABC)

Result = #No Match (Incorrect Result for DC XYZ)

Formula 2a - 1st part of formula 1 pulling in data from DC ABC

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false)

Result = $500 (Correct Result)

Formula 2b - 2nd part of formula 1 pulling in data from DC XYZ

IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)

Result = $400 (Correct Result)

Formula 3 - Pulling data from DC ABC and XYZ

=VLOOKUP([Lane Concat w/ Supplier Name]@row, IF(CONTAINS("ABC", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - ABC Range 2}, IF(CONTAINS("XYZ", [DC Location]:[DC Location]), {3A. DC Rate Capture Sheet - XYZ Range 1}, "")), 26, false)

Result = $500 (Correct Result for DC ABC)

Result = #No Match (Incorrect Result for DC XYZ)

Paul Newcome

What about screenshots of each?

Garrett.Ricker

what is noted above is what would show in the screen shots. Not able to send over screenshot due to sensitive material.

Paul Newcome

What about just screenshots of the columns you are matching on? Unfortunately there is no further troubleshooting we can do without being able to visualize things, and you may need to just reach out to Support.

Garrett.Ricker

Here is the destination sheet showing the fields being used minus the sensitive information. 1st photo is of DC "ABC". 2nd photo is of DC "XYZ"

Here are the screen shots from the source sheets. 1st photo is of DC "ABC". 2nd photo is of DC "XYZ"

=IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - ABC Range 1}, 26, false), IF(CONTAINS("XYZ", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/ Supplier Name]@row, {3A. DC Rate Capture Sheet - XYZ Range 1}, 26, false)))

This is as much as I can show. Thank you for all the help up to this point. Hope this allows for resolution.

Paul Newcome

Let me ask this...

Is there a reason you are searching for "ABC" or "XYZ" in the entire [DC Location] column as opposed to just [DC Location]@row?