Matching a text cell to the closest cell in a standard list of products

[tonyhewson]

Im looking to find the closest match to a cell in a list.

So I have two column,

=LEFT(A5,4)&"x"&LEFT(B5,2) as the example
Frontage Depth Product '
10.7 26 10.7X26


standard products rate per metre
10.0X21 $400 per sqm
12.7 21 $430
12.7X21 $500
10.5X25 $650
10.5X28 $700
10.5X30 $545
10.5X32 $635
12.0X21 $720
12.0X25 515
12.5X21 1025
12.5X25 825

I need to take the irregular Product Type and find the closest 'Standard' Product Type. I note the multiplication of the frontage X depth while relevant is not the solution because one product could be 12m frontage depth 25 or 300sqm while another product could be 16 frontage 19 depth or 304sqm same size, different product. The primary issue is the front 10 or 12 or 14 or 16 then the depth 22 25 28 30 metres. the issue is the block designs could be 10.2X26 so its a 10mX25 product.

Each 'standard' product has a price sqm that Im will apply to the block being considered.
So block listed
10.7X26 =278,2 standard 10.5X25 = 650/sqm
Price for lot under consideration 278.2X650=$180,830

Have tried matching, the cell/block of land with list using -1 1 etcetera. what Im trying to do is find the closest match in a standard list and apply the standard per sqm rate to the size of the irregular lot....Ive tried creating an array formula but Im new to this and Ive struggled .I hope this makes sense....

TonyH

 

[palaeontology]

If you have your list of 'Standard Products' in the range B4:B14, and you entered the 'Irregular Product size' into cell D2, then ....

in cell D4 you could type ... =ABS(LEFT($B4,4)-LEFT(D$2,4))+ABS(RIGHT($B4,2)-RIGHT(D$2,2)) ... you'd then drag this formula down to D14

then in cell D3 you could type ... =INDEX($B$4:$D$14,MATCH(MIN(D4:D14),D4:D14,0),1) .. this cell tells you which 'Standard Product Size' is the best match

from there it would be a simple match to return the corresponding price rate per metre

Kind regards,

Chris

 

[tonyhewson]

Hi Chris,

Apologies for not responding.I have responded to your private message and then looked for how to reply. Its right in front of me. I am very embarrassed.

Thank-you for your response. Given the time i spent on this myself unsuccessfully, I can honestly say I was 'over the moon' when you responded, particularly as I could see elements of what I had been doing in your response. Unfortunately I couldn't get your solution to work. I can honestly say i again felt stupid. No excuse, but its the truth.

There is an area showing Thanks and Likes...I honestly dont know how to use this....Im sure its perfectly obvious but sitting here right now I dont know how.

Im sure if you do respond I will again feel embarrassed...but Id rather that and learn how to respond...so if you point me in the right direction I would welcome the opportunity to 'put things to right' as far as possible publicly.



Genuinely thanks again for helping.

 

[tonyhewson]

I think Ive managed to like your post. Its right in front of me!!!....and I think Ive managed to select Thank-you....I feel so stupid for not doing this correctly

anyhow Ive acknowledged I got it wrong and it was so simple!! my sincere apologies

TonyH