Multiple Range Lookup Possible??? (Expert Level Question?)

[Naji]

I don't know if this is possible with some sort of lookup function
combination, but maybe somebody has an idea...


I have the following table, gauge ranges shown in the first column,
and across we have width ranges:


A1 B1 C1 D1
1-2 3-4 4-5
A2 .200-.249 1 6 9
A3 .250-.274 3 6 10
A4 .275-.300 5 7 12


I need to create a lookup function where in another sheet I have the
following

Column A Column B
Enter Gauge: .243
Enter Width: 3.5
Result: 6

Where you punch in the gauge and width and it looks it up on the table
according to range. In this case, it knows that .243 is in the first
row, and width 3.5 falls under the C column, so the result is 6.


I need to find some lookup function that finds the result, based on
the input Gauge and Width, when the table is set up in ranges. Is this
possible??

 

[Jim Thomlinson]

Yes it is possible take a look at this link...
http://www.dailydoseofexcel.com/archives/2006/04/21/two-variable-lookups/

 

[Tom Hutchins]

Here is one way..

Paste this custom function in a VBA module in your workbook:

Public Function WithinRange(Target As Range, CheckRange As Range, _
RowCol As String, Optional DelimitChar As Variant) As Double
Dim a As Variant, c As Range
On Error GoTo WRerr
'Use a dash if no delimiter argument was supplied.
If IsMissing(DelimitChar) Then DelimitChar = "-"
For Each c In CheckRange
a = Split(c.Value, DelimitChar)
If (CDbl(a(LBound(a))) <= CDbl(Target.Value)) And _
(CDbl(a(UBound(a))) >= CDbl(Target.Value)) Then
If RowCol = "R" Then
WithinRange = c.Row
ElseIf RowCol = "C" Then
WithinRange = c.Column
End If
Exit Function
End If
Next c
'Not found. Return 0.
WithinRange = 0
Exit Function
WRerr:
'Return 0 for all errors.
WithinRange = 0
End Function

Your formula will call this function twice - once to find the correct row in
the lookup table, and once to find the column. You would call it like this:

=INDIRECT("Sheet1!" &
ADDRESS(WithinRange(B1,Sheet1!A2:A4,"R"),WithinRange(B2,Sheet1!B11,"C")))+(NOW()*0)

In this example, Sheet1 is the name of the sheet with the lookup data. B1 is
the guage value to look up, and B2 is the width value to look up. Edit these
values as appropriate. The +(NOW()*0) forces Excel to recalculate the
formula. The "R" argument tells it to return the row number; "C" tells it to
return a column number.

The above formula will return #VALUE if the guage or width is missing or not
found in the lookup table. To return zero instead, a longer formula is needed:

=IF(OR(WithinRange(B1,Sheet1!A2:A4,"R")=0,WithinRange(B2,Sheet1!B11,"C")=0),0,INDIRECT("Sheet1!"
&
ADDRESS(WithinRange(B1,Sheet1!A2:A4,"R"),WithinRange(B2,Sheet1!B11,"C"))))+(NOW()*0)

If you are new to macros, this Jon Perltier link may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[Naji]

Bravo Tom!! What an excellent solution...it works great!! Thanks so
much!!