Two Dimensional Lookup

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Two Dimensional Lookup

Post by jstevens »

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

Example:
untitled.png
Regards,
John
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Two Dimensional Lookup

Post by HansV »

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

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Two Dimensional Lookup

Post by 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
Regards,
John

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Two Dimensional Lookup

Post by 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
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Two Dimensional Lookup

Post by jstevens »

How would it look if the range was an array?

Regards,
John
Regards,
John

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Two Dimensional Lookup

Post by HansV »

What do you mean?
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Two Dimensional Lookup

Post by 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
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Two Dimensional Lookup

Post by 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
Regards,
John

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Two Dimensional Lookup

Post by 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
GoldLounger
Posts: 2640
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Two Dimensional Lookup

Post by jstevens »

Thank you.

Regards,
John
Regards,
John