CODEWITHSUNDEEP
excelformula
ali_saigh
ali_saigh

Reputation: 21

Merging 2 excel codes

I have two codes in two different columns, and i wanted to combine them together.

the two codes are working fine but when i try to combine them together i don't get the right output

For example the first code takes value from CELL C6 and the code is 

=AND(B3<=VLOOKUP(C6,Sheet3!A2:C35,3,FALSE),B3>=VLOOKUP(C6,Sheet3!A2:C35,2,FALSE))

And it should return either TRUE or FALSE witch it works fine.

The second code takes value from CELL D6 and the code is 

=AND(B3<=VLOOKUP(D6,Sheet3!A2:C35,3,FALSE),B3>=VLOOKUP(D6,Sheet3!A2:C35,2,FALSE))

And it should return either TRUE or FALSE witch it works fine.

But when i try to combine them together with OR Statement, the code does not work.

The combined code is 

=OR(AND(B3<=VLOOKUP(C6,Sheet3!A2:C35,3,FALSE),B3>=VLOOKUP(C6,Sheet3!A2:C35,2,FALSE)),AND(B3<=VLOOKUP(D6,Sheet3!A2:C35,3,FALSE),B3>=VLOOKUP(D6,Sheet3!A2:C35,2,FALSE)))

With the combined code, i want it to return true if the cell C6 or the cell D6 is true, but code returns a strange output when both cell C6 and cell D6 are true which is not what i want.

The problem is the output will not retrieve TRUE unless both cells cell c6 and d6 are true other wise it will not even retrieve false, the output will just be N\A

The evaluation of the combined code is shown in this photo bellow.

The combined code evaluation

Will you help me please ?.

Upvotes: 1

Views: 41

Answers (2)

user4039065
user4039065

Reputation:

Supply extreme values with IFERROR that will never be true to cover situations where no match is found.

=OR(AND(B3<=iferror(VLOOKUP(C6, Sheet3!A2:C35, 3, FALSE), -1e99), 
        B3>=iferror(VLOOKUP(C6, Sheet3!A2:C35, 2, FALSE), 1e99)),
    AND(B3<=iferror(VLOOKUP(D6, Sheet3!A2:C35, 3, FALSE), -1e99),
        B3>=iferror(VLOOKUP(D6, Sheet3!A2:C35, 2, FALSE), 1e99)))

Upvotes: 0

teylyn
teylyn

Reputation: 35925

You are using Vlookup with the False parameter as the fourth argument in both conditions.

If VLookup does not find an exact match, it will return #N/A!

You need to wrap each of your Vlookups into an IFError to translate that error to an empty string, so the comparison does not return an error, but a "false" instead.

=OR(AND(B3<=iferror(VLOOKUP(C6,Sheet3!A2:C35,3,FALSE),""),B3>=iferror(VLOOKUP(C6,Sheet3!A2:C35,2,FALSE),""),AND(B3<=iferror(VLOOKUP(D6,Sheet3!A2:C35,3,FALSE),""),B3>=iferror(VLOOKUP(D6,Sheet3!A2:C35,2,FALSE),"")))

I just free-handed the Iferror edits in your formula. You may want to use the formula syntax helper to place the Iferror brackets correctly.

Upvotes: 2

Related Questions