IF(ISNA on vlookup.

[macke]

I have the following formula, which works absolutely fine. I now need it to come up with the words "NO PRICE" instead of #N/A where the STOCK_CODE doesn't exist in Price!$A$3:$A$3000. I have tried =IF(ISNA, but cannot get the syntax right.


=INDEX(Price!$B$2:$H$2,MATCH(MIN(INDEX(Price!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0)),INDEX(Price!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0),0))

Ken

[henrikoz]

Hi!

I use to cut the whole formula and the use a IF formula and then write like:

=IF(ISERROR(INDEX(Price!$B$2:$H$2,MATCH(MIN(INDEX(Price!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0)),INDEX(P rice!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0),0))),"No Price",(INDEX(Price!$B$2:$H$2,MATCH(MIN(INDEX(Price!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0)),INDEX(P rice!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0),0))))

[daddylonglegs]

You only really need ISNA on the MATCH, i.e.

=IF(ISNA(MATCH(A8,Price!$A$3:$A$3000,0)),"No Price",INDEX(Price!$B$2:$H$2, MATCH(MIN(INDEX(Price!$B$3:$H$3000, MATCH(A8,Price!$A$3:$A$3000,0),0)),INDEX(Price!$B$3:$H$3000,MATCH(A8,Price!$A$3:$A$3000,0),0),0)))