Complex matching with ranges
 

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 a
1 25.03 1.25.03

And sheet 1 values a
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!!!!

Complex matching with ranges
 

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,A BS(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


"Steven M." wrote in message
...
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 a
1 25.03 1.25.03

And sheet 1 values a
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!!!!