Help With formula

[Ancient Wolf]

Hello MrExcel. I find myself needing help once again. This time I am needing help with a formula.

I am trying to figure out how to make this formula:

=IF(ISERROR(INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2)),"",INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2))

only work when a specific named range is selected from a drop down list in cell B3. In short, I want it to be If B3= amz and B172 = 200, then make I172 = B004LRYO7I, but without having to write multiple IF formulas.

I've tried entering an AND statement into the formula, but depending up where I enter it, the formula either appears to ignore the AND statement, or the formula will report either True or False.

Please help.

For sample purposes:
The named range that needs to be selected in the drop down in B3 is amz.
The numbers that the formula looks for in cell B172 are 200, 312, 313, 314, and 315.
The information in the table that it is supposed to return is B004LRYO7I, B004LS4BAC, B004LS7YFG, B004LS4UQC, B004LSBUTM

 

[Desu Nota from Columbus]

=IF(ISERROR(INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2)),"",IF(AND(B3="amz",B172=200),INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2)))



The INDEX(...) will only take place now if these two values are true. Is this what you needed?

 

[Desu Nota from Columbus]

Are you saying:

IF the following is true perform the index function?

B3 is amz

-and-

B172 is 200 or 312 or 313 or 314 or 315


=IF(AND(B3="amz",OR(B172=200,B172=312,B172=313,B172=314,B172=315)),INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2),"")

If none of the BLUE font is true, then this formula returns a "" (blank).

 

[Ancient Wolf]

Thank you for responding Desu Nota from Columbus. I did not receive any email notification letting me know that anyone responded and was just about to delete this.

I manage to fix this issue myself with this formula:

=IFERROR(IF(B3=amz,INDEX(Tables!AS2:AT23,MATCH(B172,Tables!AS2:AS23,0),2),""),"").

I think this is similar to what you suggested.

Thank you for the help anyway.