A function to find best close match from a large list

Question

Hi all,

Using MS-Office, Excel 2010 I need a formula or macro that helps me in following way:

I have a large list of unique random integer numbers (Code) between 1 and 10000000000 in column A, 35000 rows, also there is a price (between $0.01 and $2.00) assigned to each Code in column B. The prices are not unique obviously. In another sheet I have a different list of random numbers that may have a match Code in the first list or may not. If it was the first case it could be easy to find the price for the number in the second sheet from the first. What if there was no match? Here I make my point by the following example: If the number in sheet 2 was 1346 and there was no match in the  column A/sheet 1 to get its price then I need to look for 134 in the column A/sheet 1, if there was no 134 then I will look for 13 and finally if the number 13 exists in the first sheet then I can return its price into the second column/sheet 2. I wrote this function: "(b1-(mod(b1,10^a1)))/10^a1" to check all possible numbers one by one manually. I would like to create a function or macro that can handle this task automatically.

I hope this is clear enough, please let me know if it is not. 

Answer 1

With the number 1346 in cell B1, Array enter this formula (enter using Ctrl-Shift-Enter) to return the price from column B:

=INDEX(Sheet1!B:B,LOOKUP(100000,MATCH((B1-(MOD(B1,10^( LEN(B1) - ROW(INDIRECT("A1:A" & LEN(B1)))))))/10^(LEN(B1)-ROW(INDIRECT("A1:A" & LEN(B1)))),Sheet1!A:A,FALSE)))

And if you want to know what the value found in column A actually was, use this array-entered formula

=INDEX(Sheet1!A:A,LOOKUP(100000,MATCH((B1-(MOD(B1,10^( LEN(B1) - ROW(INDIRECT("A1:A" & LEN(B1)))))))/10^(LEN(B1)-ROW(INDIRECT("A1:A" & LEN(B1)))),Sheet1!A:A,FALSE)))

Note that the LOOKUP(100000  just needs a number that is greater than the number of rows used for the data.

Answer 2

Thanks for reply.

I have 1346 (as an example) in cell B1 in  sheet 2 and I pasted the formula you have written here in cell C1 sheet 2, when I press Return key it gives me error "#N/A". I am not sure what do you meant by "Array enter". I tried it by Ctr+Shft+Enter and nothing happened. What part am I missing?

Answer 3

Select the cell  with the formula, press F2 to enter Edit mode, then immediately press Ctrl-Shift-Enter. If you do it properly, Excel will put { } around the formula and should give a return. Note that you cannot type the { } - you need to properly array enter the formula.

Answer 4

I was able to do "Array Enter" the formula properly! The answer was not what I was expecting. When a number that is in the large list was placed in Cell B1 the formula returned the same number as answer in Cell C1. Please look at the attachment:

I had to have $0.007 in cell C2, $0.052 in cell C3, $0.065 in cell C4 and $0.016 in cell C5. They are the prices assigned to each code in sheet1 column B.

I appreciate your helps

Answer 5

You used the A:A (confirm the used Code number) and not the B:B version (return the price). Array enter this:

=INDEX(Sheet1!B:B,LOOKUP(100000,MATCH((B1-(MOD(B1,10^( LEN(B1) - ROW(INDIRECT("A1:A" & LEN(B1)))))))/10^(LEN(B1)-ROW(INDIRECT("A1:A" & LEN(B1)))),Sheet1!A:A,FALSE)))

And this uses a simplified version of your MOD function (INT)

=INDEX(Sheet1!B:B,LOOKUP(100000,MATCH(INT(B1/10^(LEN(B1)-ROW(INDIRECT("A1:A" & LEN(B1))))),Sheet1!A:A,FALSE)))

Answer 6

Hi,

I used this function: INDEX(sheet1!B:B,LOOKUP(100000,MATCH(INT(B2/10^(LEN(B2)-ROW(INDIRECT("A1:A" & LEN(B2))))),sheet1!A:A,FALSE))) and it works fine. For my information, how come I did not need to do "Array Enter" anymore? Can you please give me some information about it?

Thank you so much.

Answer 7

Probably because we are using the array form of the LOOKUP function. The other one doesn't need C-S-E either... but there is no harm in using CSE for regular functions.