# Two way lookup with a range of cells

#### babywong

##### New Member
Hi, I have a 2 way lookup that I can use index and match function for, but the problem is my columns and rows are in ranges. For example

20-25 26-30
0-5 2 4
6-10 4 6
11+ 6 8

The index and match function will work if I create a table column 0, 1, 2, 3, 4, 5, etc and row with 20, 21, 22, 23, etc.

And the Vlookup will work, assuming I don't have a double lookup.

How do I do a vlookup with ranges in the columns and rows?

Kathy[/img]

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### pgc01

##### MrExcel MVP
Hi Kathy
Welcome to the board

You did not define exactly the syntax of the row/column values.

I assumed:

Either (low limit)-(high limit) Example 20-25

Or (low limit)+ Example 11+

With these assumptions I post a solution.

Remark. If the syntax for the row/column values is more complex maybe it's better to do this in vba, using a with a UDF. Even with only these 2 cases the formula is already somewhat complex and you might already want to do this with vba.

Hope this helps
PGC

\$B\$8,LEN(\$B\$6:\$B\$8)-FIND("-",\$B\$6:\$B\$8))+0),ROW(\$B\$6:\$B\$8)-ROW(\$B\$6)+1),IF(G\$6>=LEFT(\$B\$6:\$B\$8,FIND("+",\$B\$6:\$B\$8)-1)+0,
ROW(\$B\$6:\$B\$8)-ROW(\$B\$6)+1))),MIN(IF(ISNUMBER(FIND("-",\$C\$5:\$D\$5)),IF((G\$7>=LEFT(\$C\$5:\$D\$5,FIND("-",\$C\$5:\$D\$5)-1)+0)*(G\$
7<=RIGHT(\$C\$5:\$D\$5,LEN(\$C\$5:\$D\$5)-FIND("-",\$C\$5:\$D\$5))+0),COLUMN(\$C\$5:\$D\$5)-COLUMN(\$C\$5)+1),IF(G\$7>=LEFT(\$C\$5:\$D\$5,FIND(

#### pgc01

##### MrExcel MVP
And this is a vba solution.

- It accepts a looser syntax, with possible spaces between the numbers and the signs, ex. "2-4", "2 -4", "2 - 4", "11 +"
- the rows/columns can be defined with just one number (like in the example I post in the columns' headers there's a cell with just 26).

Hope this helps
PGC

Code:
``````Function RangesGetValue(rTable As Range, lTableRow As Long, lTableColumn As Long)
Dim lRow As Long, lColumn As Long

lRow = GetCoordinate(rTable.Columns(1).Resize(rTable.Rows.Count - 1).Offset(1).Cells, lTableRow, True)
lColumn = GetCoordinate(rTable.Rows(1).Resize(1, rTable.Columns.Count - 1).Offset(, 1).Cells, lTableColumn, False)
RangesGetValue = Cells(lRow, lColumn)
End Function

Function GetCoordinate(rVal_Headers As Range, lVal As Long, bRow As Boolean)
Dim rCell As Range, oMatch As Object

With CreateObject("vbscript.regexp")
.Pattern = "^(\s*(\d+)\s*|\s*(\d+)\s*\-\s*(\d+)\s*|\s*(\d+)\s*\+\s*)\$"

On Error Resume Next

Set oMatch = .Execute(rCell.Value)(0)
If Err > 0 Then
Err.Clear
Else
If (oMatch.submatches(1) = lVal) Or _
((oMatch.submatches(2) <> "") And (oMatch.submatches(2) <= lVal) And (oMatch.submatches(3) >= lVal)) Or _
((oMatch.submatches(4) <> "") And (oMatch.submatches(4) <= lVal)) Then

GetCoordinate = IIf(bRow, rCell.Row, rCell.Column)
Exit For
End If
End If
Next rCell
End With

End Function``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,139
Messages
5,835,632
Members
430,372
Latest member
contentment

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back