Formula needed to find the closest two numbers to a set of two numbers when an exact match is not going to be likely?

[tonywatsonhelp]

Hi Everyone,

I've got an interesting one for you,

I have a list of numbers in two columns, with there reference number next to them,

now I have another list of just numbers in cloumns

I need help doing the following

1 Finding exact matches of the two numbers from the list and pulling the refrence numbers out,
2 finding the exact matches for either the first or second column, then finding the nearest number in the other column
3 Finding the nearest number in either column and then finding the nearest number in the other column?

So tio try explain in more detail,

Column A Reference Number,
Column B First Number (has 4-6 decimal paces like 123.7654)
Column C Second number,(has 4-6 decimal paces like 125.7654)

D blank

E2 = the first number I want to reference for example: 67.897
F2 = the 2nd number I want to reference for example: 100.001

G2 is where I want my results,

So if I was to put my formula in English it would read like this,

=look at the number in cell E2, look down column B and see if it has a match, if it does look at the value in column F2 and look down column C to see if it has a match,
if any of the matches of E2 or in the same row as F2, success! put the Ref Number in Column A of that row in G2, if not look down column B and see if there are any matches, if there are look down colmn C and see if there are any matches if there are matches in both work out which one has the closest number in the other row, and use that row into G2,
If no matches are found, find the closest Column B number to cell E2 and the closest Column C to F2,
work out which one is closer and use that one

II'm getting very confused with this as I can't just check one column then the other as its not quite that simple, please help

Tony

 

[Snakehips]

Tony,

Can you clarify the scale of data....
Referencing just two numbers E2 & F2 to give one RefNo in G2?
Max rows of data in A:B:C is in the order of ??

Will there ever be more that one match of E in B and of F in C ?

No guarantees that that will give me a full understanding of your conditions.


Any chance of posting an illustrative snapshot or two showing say 10 rows of data and a valid result?

 

[tonywatsonhelp]

Sorry Snake hips, I just read my post back and boy is it confusing! lol
It goes to show how flustered I'm getting, it so made sense when I read it back before! lol

I'll try simplify it,

I have two Cloubmns of numbers, Column B and Column C, both Column will have dupicates in them as they are our old referece numbers so relate to very different systems.
A few years ago I Introduced a new Referencing System, much better and easier, however some products that we hardly ever sell have had there old number still on them, (the idea was instead of chage every product code t the time which would have taken days, change the most populare and then each time one of the unpopular items was sold if it had an old number on it it needs to changed, but nobody has been doing this, I now have the yearly stock take to do so I've now got the job of changing all these codes over.

So if I explain what the codes mean it might help you to understand what im trying to do,
We sell parts, every type of part you can imagine from nuts and bolts to gun triggers, each Part has a two number id, 123456 / 98765 for example.
These numbers used to be fairly random and for every single part we ever made!
I created a way we could catgurise the parts by number, so all metal products begin with 11 for example and so on, so I now have a list of only 43000 product numbers, we used to have over 1 million.
So Column B and Column C are the numbers Ive associated with the new product Numbers so I have a list of just under 43000 in A its the new simple product number for example "11B34C56" B & C are the old product numbers, So for this one for example it could be the code is 123456/78965, however as I said we had over 1,000,000 of these codes so as you can imagine I'm not going to assign 1,000,000 code a prodct number I just need a rule, and the rule is simple, the product code is matched to the number it is closest to! so al I'm trying to do is look at the numbers I have in cell E2 &F2 to start, and find the very closest match to those two numbers in a single row looking down Columns B and C, match B to E2 and C to F2, then returning the new Ref Number in Column A for that match!
Hope that helped a little

Tony

 

[Snakehips]

Tony I have to be honest and say that I'm still not clear on this.
To the best of my initial understanding I thought I had a 'solution' that I felt was close to giving your 'best match' of E2 & F2 with B:C data.
However I think I'm now way off the mark as it is certainly not something that can be dragged down to give any sort of result for multiple rows of E & F.

This might have to be a vb. solution?

I think your best bet would be to post a snapshot or maybe even a link to a small workbook with a little sample data.

 

[tonywatsonhelp]

Ok mate, well thank you for trying, If you do think you have a solution to the best match for E2 & F2 it would still be of use to me, I'm strange but its surprising how often I can adapt something once I know how to get started so I'd very much appreciate it if you could post that, as I've got no where with this and can honestly say I'm completely lost! at least If I can see it works for one row I can see what I could do to make it work on others or I might even be able to write a vba code based on that code,

Thanks

Tony

 

[Snakehips]

Easiest way is if you look at this....
I may be way off beam or not based on your initial post but this was the way I was coming at it.
https://www.dropbox.com/s/ovftq2y54i4uy3c/TWH.xlsx?dl=0

It involved some helper columns / cells which, as is, don't make it scaleable.

I would be happy to explore other avenues if you can share some realistic data.

Time for bed where I am!!

Tony

 

[tonywatsonhelp]

Thank you mate, your a big help again have a good sleep