Thread: looking for the closest price to a reference price

The Array you outline should work... in essence it matches the ABS variance of each data point (from target value) to the MIN ABS variance for all data points and returns the associated value.

Presumably you're entering this as an Array - ie with CTRL + SHIFT + ENTER rather than Enter as normal - once Array is set the formula will become encased within { }

A sample workbook would help - remove / dummy anything confidential.

You need to account for the blanks - these will otherwise be coerced to 0 and 0 is closest value to target, eg:

B4:
=INDEX($E4:$O4,MATCH(MIN(IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4))),IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4),""),0))
confirmed with CTRL + SHIFT + ENTER
copied down

EDIT:
If preferred - to avoid errors from displaying - precede the above with a COUNT test of E:O to ensure at least one entry before conducting the Array

For returning the value from row 2 - assuming they may change (ie Position less 1 not necessarily valid)

=IF(ISNA($B4),"",INDEX($E$2:$O$2,MATCH($B4,$E4:$O4,0)))

(if you opt for COUNT test to prevent NA (displaying Null instead) then modify ISNA to ="" test)

Originally Posted by PeterO'Connor

I need to find the closest match given that some cells are blank or have " ". This has caused error values to come back regardless of whether I use the suggested countif function as a workaround.

I'm afraid I don't understand Peter.

The suggested Array validates on an ISNUMBER basis so whether your data set includes spaces or blanks amongst the numerics is irrelevant - they're simply ignored.

The pre-emptive COUNT test was merely to demonstrate that you can limit the times the suggested Array is invoked by validating as to whether or not any numerics exist in the range to start with - if not there's no need to validate, eg:

B4:
=IF(COUNT($E4:$O4),INDEX($E4:$O4,MATCH(MIN(IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4))),IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4),""),0)),"")
confirmed with CTRL + SHIFT + ENTER
copied down

I'm afraid at this point I think you need to post a sample which demonstrates the above returning erroneous results.

Originally Posted by PeterO'Connor

The thing is;
when a lookup returns a blank or " ", it renders "=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0))" ineffective, regardless of whether there are other values across the row - I just get an error.

correct, but the above formula was not the formula I suggested you use (for that reason).

Originally Posted by PeterO'Connor

Next problem:
In the previously attached example, cell B4, C4 and D4 function properly but if change F4 to 101, C4 and D4 cease to function properly. This might be related to the fact that it is in excess of 100. Further, if you change to F4 to -101, cells B4, C4, D4 fail to work.

Let's revisit the above once you've tested the formula suggested in my prior post.

I don't mean to be a bore nor sound like a broken record but please post a sample file with the prior suggestion in place which illustrates the errors you're experiencing ... I for one can not replicate.

Originally Posted by PeterO'Connor

I have used that but have found that things only function when the value is less than Primary Price.

Using your earlier file, with the suggested formula in B4 and copied down to B22, I get:

99.0000
105.0000
5.0000
[Null * 6]
(1.3000)
[Null * 9]

The 2nd & 3rd results are both > primary price (99.88, -0.21 respectively) which goes against your assertion above.

For the sake of transparency I've attached your earlier file with the suggested formula in place (B4:B22)...

I've also removed the formula from C and adjusted that in D so as to return the appropriate values.