Find a matching value in column then in row

Question

This is difficult to explain in the question title. I have a multi-page Excel file that I need to do a look up in. Sheet1 has the values that I'm looking for and sheet2 has where I want to find them.

On sheet1 I have a Service Code in column E and a Unit Cost in column J.

Then on sheet2, I have a list of Service Codes in Column A with their corresponding prices in columns D:N.

I need to find the Service Code and then return the matching Unit Cost if there is one. If there is not a matching Unit Cost I need to know if there is a Unit Cost that is within .01 of the lookup Unit Cost.

Sheet1 Sample

Contract Numner         Revision    Service Code        Unit Cost
441-01568221-BKT        0           RD190A              2.04
441-01568221-BKT        0           PLA10A              156.44
441-01568221-BKT        0           PL156A              312.89
441-01568221-BKT        0           RD190C              2.16

Sheet2 Sample

Service Code    CT01    CT02    CT03    CT04    CT05    CT06    CT07    CT08    CT09    CT10    CT11
RD185C          $2.10   $2.10   $2.40   $2.30   $2.00   $2.00   $2.00   $2.00   $2.00   $2.00   $2.00
RD190A          $2.05   $2.05   $2.34   $2.24   $1.95   $1.95   $1.95   $1.95   $1.95   $1.95   $1.95
RD190B          $1.94   $1.94   $2.22   $2.13   $1.85   $1.85   $1.85   $1.85   $1.85   $1.85   $1.85
RD190C          $1.89   $1.89   $2.16   $2.07   $1.80   $1.80   $1.80   $1.80   $1.80   $1.80   $1.80

So in this example, I put my formula in column O and would like it to show for Service Code RD190A that there are no exact matches but that CT01 is off by .01. And to show that for Service Code RD190C that there is an exact match in CT03.

What I've tried is this:

=INDEX(Sheet2!D2:N2497,MATCH(E5909,Sheet2!A2:A2497,0),MATCH(J5909,"sheet2!$d$"&MATCH(E5909,Sheet2!A2:A2497,0)&":$N$"&MATCH(E5909,Sheet2!A2:A2497,0)),0)

It returns an error saying that:

A value used in the formula is of the wrong data type

On sheet1 there are more than 12000 Service Codes:Unit Cost pairs and on sheet2 there are just shy of 2500 unique Service Codes.

EDIT

I have tried adding this to the formula

=INDIRECT("'Sheet2'!$D$"&MATCH(E5909,Sheet2!A2:A2497,0)&":$N$"&MATCH(E5909,Sheet2!A2:A2497,0),"")

But it doesn't work. I can get this to work:

=INDIRECT("'Sheet2'!$d$"&MATCH(E5909,Sheet2!A2:A2497,0)&"",TRUE)

But it doesn't get me what I need either.

Answer 1

To get the ABS minimum difference,

=AGGREGATE(15, 6, ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0)), 1)

To get CT-01 for RD190A and CT-03 for RD190C,

=INDEX(Sheet11!$1:$1, AGGREGATE(15, 6, COLUMN(D:N)/(ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0))=AGGREGATE(15, 6, ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0)), 1)), 1))

First get the smallest ABS (i.e. absolute value) difference in pricing. Next use that figure in a similar wrapping function to retrieve the CT-xx number.