Complex matching with ranges

  • Thread starter Thread starter Steven M.
  • Start date Start date
S

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!!!!
 
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
 
Back
Top