Excel: =IF(ISNA....not working, too many arguments 2

[coolcarls]

Anyone have a better way around this mess?
If I put a "=IF(ISNA" in the formula I get "too many arguments"

=INDEX(Sheet1!$A$1:$B$21, MATCH(H23,Sheet1!$A$1:$A$21,), MATCH("Price",Sheet1!$A$1:$B$1,))

Thanks in advance
Carl

 

[KenWright]

=IF(ISNA(Your_Formula),"",Your_Formula)

=IF(ISNA(INDEX(Sheet1!$A$1:$B$21, MATCH(H23,Sheet1!$A$1:$A$21,), MATCH("Price",Sheet1!$A$1:$B$1,))),"",INDEX(Sheet1!$A$1:$B$21, MATCH(H23,Sheet1!$A$1:$A$21,), MATCH("Price",Sheet1!$A$1:$B$1,)))

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[byundt]

If the Price part of your formula is always going to work, then you can put the other MATCH function inside an ISNA function like this:

=IF(ISNA(MATCH(H23,Sheet1!$A$1:$A$21,)),"",INDEX(Sheet1!$A$1:$B$21, MATCH(H23,Sheet1!$A$1:$A$21,), MATCH("Price",Sheet1!$A$1:$B$1,)))

Also, I notice that you omitted the third parameter for your MATCH function. This means that column A has to be sorted in ascending order and that approximate matches would be acceptable. If you require an exact match (or don't want to sort your table), then you need a third parameter of 0:

=IF(ISNA(MATCH(H23,Sheet1!$A$1:$A$21,0)),"",INDEX(Sheet1!$A$1:$B$21, MATCH(H23,Sheet1!$A$1:$A$21,0), MATCH("Price",Sheet1!$A$1:$B$1,0)))

Finally, your data is set up for a VLOOKUP, so you may prefer either of:

=IF(ISNA(VLOOKUP(H23,Sheet1!$A$1:$B$21,1,FALSE)),"", VLOOKUP(H23,Sheet1!$A$1:$B$21, MATCH("Price",Sheet1!$A$1:$B$1,0),FALSE))
=IF(ISNA(VLOOKUP(H23,Sheet1!$A$1:$B$21,1,FALSE)),"", VLOOKUP(H23,Sheet1!$A$1:$B$21,2,FALSE))

The second formula assumes that you want the data from column B.
Brad

 

[coolcarls]

Ken, Thanks! It works perfectly.
byundt, I went with your last formula which is also flawless.
I can't thank you enough, I learn so so much here.
Have a beer on me!
Cheers,
Carl