Complex matching with ranges

[Steven M.]

I am not sure how to accomplish what I need to accomplish, and I'm not sure
it can be done in excel... It may need a VB code which I have no experience
with.

I have a workbook that contains 2 worksheets. Each worksheet is approx 20k
rows of data.

Sheet 1:
Column A are numbers that can contain up to 4 digits. There are repeating
numbers. An example is 1 = product type 1, 548 = product type 548, etc.

Column B is formated as numbers and is in the form of XX.XX. These also may
be repeating numbers. An example is 23.45, or 1100.50, etc.

Column C is a concatenation of column A and B. It is read as text since
there are 2 decimals inserted in the number. =A1$"."$B1, so the returned
value is 1.23.45, or 548.1100.50, etc.

Sheet 2 is formated the same as sheet 1. The values contained in the sheets
are different.

I need to use Sheet 2 values to match to sheet 1 values and return the
closest value, higher or lower.
Example:
Sheet 2 Column A must match Sheet 1 Column A. Using sheet 2 column B I want
to search sheet 1 column b and return the closest value +/- 1.00 to the sheet
2 column b value while having an exact match to column A.
If Sheet 2 values are:
1 25.03 1.25.03

And sheet 1 values are:
1 24.78 1.24.78
1 25.33 1.25.33

The returned value should be 1.24.78 since it is only .25 from the lookup
value and 1.25.33 is .30 from the lookup value.

Can this be done?

Thanks!!!!

 

[Bernie Deitrick]

Steven,

Here is a solution that ignores your existing column C. Insert a new column C on sheet 2, and in
cell C2, array enter (enter using Ctrl-Shift-Enter) this formula:

=A2& "." &
INDEX(Sheet1!B:B,SUM((MIN(IF(Sheet1!A1:A20000=A2,ABS(Sheet1!B1:B20000-B2),1000))=ABS(Sheet1!B1:B20000-B2))*ROW(Sheet1!A1:A20000)))

and copy down to match. This will return the closest value (no matter how far away) unless there is
an _exact_ tie (unlikely because of how numbers are represented in Excel's memory).

HTH,
Bernie
MS Excel MVP