Multiple Logical Tests in an IF function

Hello,

I have a sort-of working formula I need help with:

=IFERROR(IF(ISNA(MATCH($F$9,AxisVAD,0)),VLOOKUP(A17,'SEARCH THIS'!A:D,4,FALSE),VLOOKUP(A17,'SEARCH THIS'!A:D,3,FALSE)),"")

The formula tests if cell F9 matches a value in a defined range (I will notethat these are text values) and if it's positive it returns one value, and if not, it returns another. My problem is that even if the value does not match the defined range, the formula still returns the first value option.

So... here's what I need it to do:

Does value match AxisVAD? NO: does value match AxisBBD? No: return nothing. OR Does value match AxisVAD? Yes: return _________. OR Does value match AxisVAD? No: Does value match AxisBBD? YES: return _________. .

Please help!

I do not believe you are using the MATCH function correctly. MATCH will not compare two values, it will find a value in an array and return it's position relative to the first cell of that array.

If you have in A1:A5 the letters A B C D E, for example, and you use =MATCH("C",A1:A5,0) the result will be 3, because "C" is in the third position of the array defined.



If you are comparing two values to see if they are the same, all you need to do is

Try;

=IFERROR(IF(ISNA($F$9=AxisVAD,VLOOKUP(A17,'SEARCH THIS'!A:D,4,FALSE),VLOOKUP(A17,'SEARCH THIS'!A:D,3,FALSE)),"")

Edit: Read the rest of the post, since it is a range you could use match with an operator such as > <

=if(match(a1,a1:15,0)>0,"true","false")
This will give a positive result greater than zero if the lookup value is anywhere in the array

Nest another IF statement in there

IF(logical test, true, IF(another logical test, true, IF(and another logical test, true, false), false2)

BOLD if will run when the first if comes back false
ITALIC if will run when both the BOLD and first if come back false

=IF(ISNA(MATCH($F$9,AxisVAD,0)), <-----There is the test for the IF
VLOOKUP(A17,'SEARCH THIS'!A:D,4,FALSE), <------ this happens if TRUE
(ISNA(MATCH($F$9,AxisBBD,0), <------- This happens if FALSE
VLOOKUP(A17,'SEARCH THIS'!A:D,3,FALSE)))) <--------- This is the extra argument, when should this run?

Can you upload a sample of exactly what you are trying to achieve?

How about this:
=IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,IF(ISNA(MATCH($F$9,AxisVAD,0)),4,IF(ISNA(MATCH($F$9,AxisBBD,0)),3,"")),0),"")

Originally Posted by scrouse14

Ok, then let me put it this way:

IF(ISNA(MATCH($F$9,AxisVAD,0)), <-----There is the test for the IF
VLOOKUP(A17,'SEARCH THIS'!A:D,4,FALSE), <------ this happens if first logical test is TRUE
(ISNA(MATCH($F$9,AxisBBD,0), <------- This is the second logical test I need to run IF the first test is false
VLOOKUP(A17,'SEARCH THIS'!A:D,3,FALSE)))) <-------- This needs to happen if the second logical test is TRUE

I need the entire formula to return nothing if neither are true. I need two logical tests in 1 formula.

That is not a second logical test, there is no IF statement in there, the bold row is the FALSE statement of the first IF.

The reason there are too many arguments, is you essentially have four arguments for one IF statement, when an IF statement only have logical_test , value_if_true , value_if_false

Maybe you can try to use ISNUMBER(MATCH(...)), instead of ISNA

I tried with my formula:
=IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,IF(ISNUMBER(MATCH($F$9,AxisVAD,0)),4,IF(ISNUMBER(MATCH($F$9,AxisBBD,0)),3,"")),0),"")
means if match VAD, gives 4, if match BBD, gives 3, if not, gives "", as column_number for VLOOKUP

=IFERROR(MATCH($F$9,AxisVAD,0),
IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,4,FALSE),
IFERROR(ISNA(MATCH($F$9,AxisBBD,0)),
IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,3,FALSE),"Nothing matched"))))

If you can upload a sample file, this will be solved much more quickly

You are getting 13 because IF(ISNA(MATCH($F$9,Sheet1!$O$8:$O$20,0) returns the number 13, because you are looking for "Johns House" in a range in which "Johns House" is listed on the 13th row. MATCH does not test for a values existence in a range, it returns the index in which that value exists.

Add a >0 to the end of the match, and now it is a test. Because if the value is greater than 0, it will return a TRUE, instead of a 13. TRUE can be used by the IF function to work properly.

=IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,IF(ISNA(MATCH($F$9,Sheet1!$O$8:$O$20,0))>0,4,IF(ISNA(MATCH($F$9,Sheet1!$O$22:$O$27,0))>0,3,"")),0),"")

I'm still looking though, trying to reverse engineer this sheet!
I don't quite understand what the Addresses have to do with it and why a MATCH is being used to look for them?


Oh, if you just needed it to be blank if no name is selected in the dropdown, do this

=IF(F9="","",IFERROR(VLOOKUP(A17,'SEARCH THIS'!A:D,IF(ISNA(MATCH($F$9,Sheet1!$O$8:$O$20,0))>0,4,IF(ISNA(MATCH($F$9,Sheet1!$O$22:$O$27,0))>0,3,"")),0),""))

Something completely different.
Look at the orange cells

If you enter nothing or a type something other than the drop down options into F9, it still returns a value.

If I type nothing or something else in F9 Cells N17 and N18 are empty