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

tonyhewson

New Member
Joined
Jun 30, 2014
Messages
7
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
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top