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

[methuselah90]

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

1) Check to see if value exists on VDA Report

• If FALSE then move onto 2
• If TRUE then display "Yes" as a result

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

• If TRUE then display "Yes" as a result
• If FALSE then go onto 3

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:

Please Login or Register  to view this content.

[ThirtyTwo]

break the formula down, use 3 spare columns for evaluating what each is doing
remove the IFERROR too while your doing the tests

first column - do the vlookup
second column - do the left,1 = V
third column - if first and second columns are blank then blank
not sure you need this third part though, as if second part is not V then it's going to be blank anyway

once each part is behaving as expected, then put each part back into the single cell formula

[methuselah90]

break the formula down, use 3 spare columns for evaluating what each is doing
remove the IFERROR too while your doing the tests

first column - do the vlookup
second column - do the left,1 = V
third column - if first and second columns are blank then blank
not sure you need this third part though, as if second part is not V then it's going to be blank anyway

once each part is behaving as expected, then put each part back into the single cell formula

It all works seperately

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

which both work on their own but put them together and its a no go!

[ThirtyTwo]

hmm

i'll put these two together, see if it works

Please Login or Register  to view this content.

[martindwilson]

this bit
VLOOKUP(CONCATENATE("EMEA\",H:H),'VDA Report'!A:B,1,FALSE)

CONCATENATE("EMEA\",H:H) you cant concatenate "EMEA\" with a range h:H
well if you do
it returns the concatenation of which ever row you put the formula in
so CONCATENATE("EMEA\",H:H) placed in say a33
would be the equivalent of
CONCATENATE("EMEA\",H33)