Two Dimensional Lookup

[jstevens]

Is it possible to return the results of a two-dimensional look with VBA?

Example:

untitled.png

Regards,
John

[HansV]

There's no direct equivalent. You'll have to loop through the rows and compare the values until you find a match.

[jstevens]

Hans,

I cannot visualize what your saying, "You'll have to loop through the rows and compare...".

I'm trying to put it to code but am not successful

Regards,
John

[HansV]

Like this:

Code: Select all

For r = 2 To m ' m is the last row in the lookup table
  If Range("A" & r) = Range("E2") And Range("B" & r) = Range("F2") Then
    Range("G2") = Range("C" & r)
    Exit For
  End If
Next r

[jstevens]

How would it look if the range was an array?

Regards,
John

[HansV]

What do you mean?

[HansV]

If you want to look up values in an array, replace the cell references with array elements:

Code: Select all

For r = LBound(myArray, 1) to UBound(myArray, 1)
  If myArryay(r, 1) = Range("E2") And myArray(r, 2) = Range("F2") Then
    Range("G2") = myArray(r, 3)
    Exit For
  End If
Next r

[jstevens]

If Range("A2:C4") were assign to an array, Cells E2 and F2 could be cross-referenced and produce the result. In this case, Cell G2 or 333-444-555

Regards,
John

[sdckapr]

Yes and that is what you would do with Hans' code

Steve

Code: Select all

Dim myArray
Dim r As Integer
myArray = Range("A2:C4")
For r = LBound(myArray, 1) To UBound(myArray, 1)
  If myArray(r, 1) = Range("E2") And myArray(r, 2) = Range("F2") Then
    Range("G2") = myArray(r, 3)
    Exit For
  End If
Next r

[jstevens]

Thank you.

Regards,
John