CODEWITHSUNDEEP
excelexcel-formula
methuselah
methuselah

Reputation: 13216

Nested IF statement validating part 1 but not part 2 of conditional

I'm working on a IF statement that does the follow checks:

1) Check to see if value exists on VDA Report

2) Check to see if the first letter of the adjacent value in column H is "V"

3) Display blank result

But whenever it does not find a value at 1), instead of proceeding on to do the check at 2) it jumps all the way to 3). Any reason for this happening?

Code below:

    =IFERROR(
       IF(VLOOKUP(CONCATENATE("EMEA\",H:H),'VDA Report'!A:B,1,FALSE)<>"","Yes",
       IF(LEFT(H:H,1)="V","Yes",""))   
    ,"")

Upvotes: 1

Views: 72

Answers (1)

Jerry
Jerry

Reputation: 71598

Well, you are using an array formula and even if you use it properly, I don't think that's what you want.

If you use 'Evaluate Formula' under the tab Formulas and use it on where it's not working, you should see that it is always only checking the first cell in H:H.

I'm not entirely sure what you are trying to do, but I would suggest something like this:

=IF(OR(IFERROR(VLOOKUP("EMEA\"&H1,'VDA Report'!A:B,2,0),"")<>"",LEFT(H1)="V"),"Yes","")

I changed the vlookup index to 2 since you're using the range A:B and it's pointless to use two columns if you use an index of 1. What I'm assuming is that you are checking for the returned value (the value that is on column B of VDA Report) rather than the lookup value.

This will check H1 only, so that you can drag this formula and it will adapt nicely.

If you are only checking if the lookup value is in column A (nothing to do with column B), then you can get simple with:

=IF(OR(ISNUMBER(MATCH("EMEA\"&H1,'VDA Report'!A:A,0)),LEFT(H1)="V"),"Yes","")

ISNUMBER(MATCH("EMEA\"&H1,'VDA Report'!A:A,0)) returns true if "EMEA\"&H1 is in 'VDA Report'!A:A.

LEFT(H1) is synonymous to LEFT(H1,1).

Upvotes: 2

Related Questions