Nested If Contains Function Returning #No Match
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)))
Answers

@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
Ipshita Mukherjee

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?

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?

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)))

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.

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.

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)

=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.

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?

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)

What about screenshots of each?

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

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.

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.

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?
Help Article Resources
Categories
Check out the Formula Handbook template!