Re: Finding the nearest match without reusing results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

In the previous post I meant to write IfError, not IsError.

- David



*Current test formula, omitting the IsError function*

=INDEX($A$9:$A$12,MATCH(
MIN(
($B5=$B$9:$B$12)*ABS($C5-$C$9:$C$12)*
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)=0)+
999999999*(($B5<>$B$9:$B$12)+
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)>0))
),
($B5=$B$9:$B$12)*ABS($C5-$C$9:$C$12)*
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)=0)+
111111111*(($B5<>$B$9:$B$12)+
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)>0)),
0))

*Formula*

IsError (Index( StoresInTable2, Match( Min(UnusedStore SalesDiffs
across CurrentState), (UnusedStore SalesDiffs across CurrentState'),
exactmatch)), "No Match")

*Errors*

IsError returns "No Match" for no match or for general snafu. Could
use IsNA to trap #N/A caused by no match, but not hide other errors
(e.g. #VALUE! caused by not array-entering formula). IsNA would have
to be nested as If( IsNA(formula), "No Match", formula), duplicating
the long Index formula.


.

Relevant Pages

  • RE: Difference between ISNA and ISERROR
    ... ISERROR is the superset of ISNA so you can replace ISNA with ISERROR but you ... ISBLANK Value refers to an empty cell. ...
    (microsoft.public.excel.misc)
  • Re: check if the contents of a cell is a time?
    ... or use IsNA instead of iserror. ... Tom Ogilvy ... > should be very simple but cant quite figure out what to write. ...
    (microsoft.public.excel.programming)
  • Re: IF Function Nesting Problem
    ... The main purpose is to remove any ... *errors *that may appear on this sheet. ... ISERROR and ISNA are not options. ...
    (microsoft.public.excel.worksheet.functions)
  • Referencing a cell that returns "N/A"
    ... You can use either ISNA or ISERROR, ... ISERROR will return zero regardless of the error. ... The cells on other worksheet either ...
    (microsoft.public.excel.worksheet.functions)
  • #N/A Replacement
    ... I searched a the "Correct #N/A Error" threads and found some info on isna and ... iserror, but I must be doing something wrong. ... not work when you have multiple active filters working. ...
    (microsoft.public.excel.misc)