Is it possible to return the results of a two-dimensional look with VBA?
Example:
Regards,
John
Two Dimensional Lookup
-
- GoldLounger
- Posts: 2642
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Two Dimensional Lookup
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 80078
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Two Dimensional Lookup
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
Hans
-
- GoldLounger
- Posts: 2642
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Two Dimensional Lookup
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
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
John
-
- Administrator
- Posts: 80078
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Two Dimensional Lookup
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
Hans
-
- GoldLounger
- Posts: 2642
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
-
- Administrator
- Posts: 80078
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 80078
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Two Dimensional Lookup
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
Hans
-
- GoldLounger
- Posts: 2642
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Two Dimensional Lookup
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
Regards,
John
John
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Two Dimensional Lookup
Yes and that is what you would do with Hans' code
Steve
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
-
- GoldLounger
- Posts: 2642
- Joined: 26 Jan 2010, 16:31
- Location: Southern California