IF with a ASNA and a VLOOKUP combination formula

[pook_666]

Hi magicians!

I have two formulas that I want to make into one, if at all possible:

Formula one:

=IF(ISNA(VLOOKUP(A2&B2,'Sheet1'!A:E,5,FALSE)),F2,VLOOKUP(A2&B2,'Sheet1'!A:E,5,FALSE))

Formula two:

=IF('Sheet1'!E2="Yes",H3*2,H3)

What I am trying to do is:
1 - vlookup A2&B2 against Sheet1 A:E
2 - if it exists, then within that vlookup table, if the value in column E is "Yes" then multiply H3*2 and if the answer is "No" simply produce H3 value.
3 - if doesn't exist, produce F2 value

So I kind of need to replace the "5" within the vlookup with formula 2? Bit weird I know, but is there anyway I can combine these into just one cell? Thanks!

 

[FormR]

Hi, how about.

=IFNA(IF(VLOOKUP(A2&B2,Sheet1!A:E,5,FALSE)="Yes",H3*2,H3),F2)

 

[pook_666]

Works like a charm - thank you!

 

[pook_666]

Sorry - I tried just putting this into my VBA code and it doesn't like "Yes" in the formula as I get the "expected end of statement" error....if I change to 'Yes' in VBA then it doesn't work in my cell. Do you know how to change the "Yes"?

VBA code is below if needed:

Range("I2").Formula = "=IFNA(IF(VLOOKUP(A2&B2,'Sheet1'!A:E,5,FALSE)='Yes',(H2/2*F2),(H2*F2)),(H2*F2))"

 

[Peter_SSs]

You have to double-up the quote marks so change "Yes" to ""Yes""

 

[pook_666]

When you look at something for so long you're brain goes to mush! Thank you @Peter_SSs !

 

[Peter_SSs]

Cheers. No problem.