IFS with VLOOKUP, AND/OR function

[JAQ1983]

Hi All,

I have 2 formula that work individually which I can't seem to merge correctly.

What I want to do is:

If B3=IND & J3 (in tab RM IND)=YES then return the vlookup OR If B3=DOM & J3 (in tab RM DOM)=YES then return the vlookup

These are the 2 individual codes that work:

=IF(AND($B3="IND",VLOOKUP($J3,'RM IND'!$U$3:$AL$10000,10,0)="Yes"),VLOOKUP($J3,'RM IND'!$U$3:$AR$10000,11,0),0)
=IF(AND($B3="DOM",VLOOKUP($J3,'RM DOM'!$U$3:$AL$10000,10,0)="Yes"),VLOOKUP($J3,'RM DOM'!$U$3:$AR$10000,11,0),0)

I have tried using IFS but this only returns the answer for the 1st statement and not the 2nd?

=IFS(AND($B3="IND",VLOOKUP($J3,'RM IND'!$U$3:$AL$10000,10,0)="Yes"),VLOOKUP($J3,'RM IND'!$U$3:$AR$10000,11,0),(AND($B3="DOM",VLOOKUP($J3,'RM DOM'!$U$3:$AL$10000,10,0)="Yes")),VLOOKUP($J3,'RM DOM'!$U$3:$AR$10000,11,0))

Can anyone advise where I have gone wrong please? Or a better way of doing it!

Cheers, Jo

 

[steve the fish]

You just put the 2nd formula into the 'value if false' of the 1st formula:

=IF(AND($B3="IND",VLOOKUP($J3,'RM IND'!$U$3:$AL$10000,10,0)="Yes"),VLOOKUP($J3,'RM IND'!$U$3:$AR$10000,11,0),IF(AND($B3="DOM",VLOOKUP($J3,'RM DOM'!$U$3:$AL$10000,10,0)="Yes"),VLOOKUP($J3,'RM DOM'!$U$3:$AR$10000,11,0),0))

 

[Eric W]

You could also make use of patterns and do something like this:

=IFERROR(IF(OR(B$3={"IND","DOM"}),IF(VLOOKUP($J3,INDIRECT("'RM " & B$3 & "'!$U$3:$AL$10000"),10,0)="Yes",VLOOKUP($J3,INDIRECT("'RM " & B$3 & "'!$U$3:$AR$10000"),11,0),0),0),0)