Lookup/Match on two values ... return one 1

[JazzyLee]

I have seen a ton of samples on looking up a table to return a value when there is a match but none on doing a lookup and matching two values then returning the third. (Or I am so confused, that I missed it) I am working on Excel 2000 (moving onto 2003 in two months) and need to do the following:
1. I have a workbook with data in Column "A", "B" and "C".
Data in "A" can repeat itself but the data in "B" will
only occur once.
e.g.) Column A Column B Column C
N/C 3106 0744
N/C 3580 1191
N/C 4223 0397
2. I need to open this workbook to lookup the value in "A"
and when there is a match, then lookup the value in "B"
to see if I have a match. If I match on "A" and "B",
then return the value in "C".
I am currently doing a VLookup with matching a single column and returning the value next to it. Now I need to match on two values then return the third value. Any suggestions or sample coding would be greatly appreciated (as always).

 

[Zathras]

There is a complex way by creating an extra column that has the values from column A and B concatenated, and then using that for the VLOOKUP. There is an even more complicated (and non-intuitive) way using SUMPRODUCT.

There is a simple way using database tecnhiques and the DGET function:

For example, if this is in A1:C7
[tt]
THING1 THING2 RESULT
A 11 101
A 22 102
A 33 103
B 11 104
B 22 105
B 33 106
[/tt]
and you assign the name MYTABLE, then if this is in E1:F2
[tt]
THING1 THING2
B 22
[/tt]
and you assign the name MYCRITERIA, then this formula
[tt]
=DGET(MYTABLE,3,MYCRITERIA)
[/tt]
returns the desired value of 105.

The requirement is that the combination of THING1 and THING2 must be unique, which you say is the case.

If you don't want to create range names, this is what the formula looks like with bare cell references:
[tt]
=DGET(A1:C7,3,E1:F2)
[/tt]
I highly recommend using range names as a general practice.

 

[JazzyLee]

The "DGET" code sounds like it would work but your example is using all the information on one sheet. How would I reference the area call "MYTABLE" from a different workbook (called "Invalid CC")? I already have columns A & B with a range name of "Invalids" and column C with a name of "Defaults" on the workbook with the table. The current data sheet values I need to reference is a variable I am holding onto that would have the match criteria for column "B" on the table and the second match criteria for column "A" on the table will be referenced with on offset of -3 from my current position on the activesheet. A code example would do wonders for my tormented soul at this time. Thanks much!

 

[Zathras]

Not sure I follow all of that.

You might try entering [tt] =DSUM( [/tt] and then use the mouse to select the ranges you need.

Alternatively, there are posts here (or maybe in the Office forum) that describe a two-step VLOOKUP process (or maybe a FAQ, I can't remember).

For the last part, take a look at the INDIRECT and OFFSET functions.

I could be of more help if you could provide a clearer description of exactly how you are set up. Test data would also be good.

 

[JazzyLee]

I can understand your state of confusion. Guess I missed the most important part of this issue. I am writing VB codes in a macro. Got any sample codes to share that would help me?

 

[Zathras]

Whether you write VBA code or not, if the data are structured in such a way that DSUM can be used, you can use it in VBA as a method of WorksheetFunction:
[tt]
MsgBox WorksheetFunction.DSUM([MYDATA],3,[MYCRIT])
[/tt]
But if your data aren't structured in a nice way, more draconian measures may be required.

 

[JazzyLee]

How do I send you a sample workbook of the table I am trying to match against? I don't see where it allows insertion of a workbook on this reply. My code that use to work when I only had two columns (A and B) looked like this:

Dim strPath_P as string

ActiveCell.Offset(0, 1).FormulaR1C1 = _
"=(VLOOKUP(RC[-3],'" & strPath_P & _
"Invalid_CC_List.xls'!Invalid_Ctrs,2,FALSE))"

which at that time the "Invalid_Ctrs" range name was column A and B (matching in A and returning what's in B). Now I need to match in A and B and return what's in C. Let me know how to send you a sample workbook and I would be more than happy to do so since I have been spinning my wheels and haven't moved and inch since yesterday.

My current code I am trying to work on looks like this:

varVisaNum = "3621"
strPath_P = "\\Cco-fil-01\CC\Processing\"
OffAddr = ActiveCell.Offset(0, -2).Address
WbName = "'" & strPath_P & "Invalid_CC_List.xls'!"
ActiveCell.Offset(0, 1).FormulaR1C1 = _
"=INDEX(" & WbName & Range(Default_Ctrs) & ", MATCH(" & OffAddr & _
",if(" & WbName & Range(Visa_No) & "=" & varVisaNum & "," & WbName & _
Range(Invalid_Ctrs) & "),0))"

But of course, I am getting an error.

 

[Zathras]

I can't go much further without a clear vision of some sample data and the expected results. In my 11:16 post, I provided one possible view of your world. How close did my guess get? Do you have column names for your data? Are you data sorted by column A, then column B?

It's starting to look like you want a function that looks something like this:
[tt]
Function TwoColLookup(AWorkbookName As String, _
CCType As String, CCNum As String) As String
:
:
End Function
[/tt]
It would return the value from the third column if found, otherwise would return some pre-defined value like "#N/A"

Easy enough to do, IF the specifications for the data being searched are completely stated.

Is that the sort of thing you are looking for?

 

[JazzyLee]

The data is sorted in the same order your sample is (column A then column B) and you are right on target with the process. I just don't know how to refer to the Invalid_CC_List workbook while going down the row of data in my active sheet. What would the code in the Function be like to return this value if a match is found?

 

[Zathras]

Ok, here is how I would probably do it. It assumes that the current sheet has ColumnA data in column A and ColumnB data in column B and you want to update column C with the corresponding code or N/A.

Put this macro in the sheet you want to update. You can leave the Invalid_CC_List sheet unopened on the disk.

Option Explicit
Const INVALID_CC_PATH = "\\Cco-fil-01\CC\Processing\"
Const INVALID_CC_LIST = "Invalid_CC_List.xls"

Sub ProcessAllValues()[COLOR=green]
'Process all pairs of data from cols A and B and put result in col C[/color]
Dim wksLocal As Worksheet
Dim wkbCCList As Workbook
Dim wksCCList As Worksheet
Dim rSearchRange As Range
Dim rMyData As Range
Dim c As Range
   Application.ScreenUpdating = False
   Set wksLocal = ActiveSheet
   Workbooks.Open (INVALID_CC_PATH + INVALID_CC_LIST)
   Set wkbCCList = Workbooks(INVALID_CC_LIST)
   Set wksCCList = wkbCCList.Sheets("Sheet1")
   Set rSearchRange = Intersect(wksCCList.UsedRange, wksCCList.Range("A:A"))
   Set rMyData = Intersect(wksLocal.UsedRange, wksLocal.Range("A:A"))
   With wksLocal
     For Each c In rMyData
       c.Offset(0, 2) = TwoColLookup(rSearchRange, c.Value, c.Offset(0, 1).Value)
     Next c
   End With
   
   wkbCCList.Close
   Set rMyData = Nothing
   Set rSearchRange = Nothing
   Set wksCCList = Nothing
   Set wkbCCList = Nothing
   Application.ScreenUpdating = True
End Sub

Function TwoColLookup(ByVal ASearchRange As Range, _
        CCType As String, CCNum As String) As String
Dim c As Range
  If ASearchRange.Cells(1, 1).Value = CCType Then
    Set c = ASearchRange.Cells(1, 1)
  Else
    Set c = ASearchRange.Find(CCType)
  End If
  If c Is Nothing Then
    TwoColLookup = "#N/A"
  Else
    Set ASearchRange = Range("B" & c.Row & ":B" & ASearchRange.Rows.Count)
    If ASearchRange.Cells(1, 1) = CCNum Then
      Set c = ASearchRange.Cells(1, 1)
    Else
      Set c = ASearchRange.Find(CCNum)
    End If
    If c Is Nothing Then
      TwoColLookup = "#N/A"
    Else
      TwoColLookup = c.Offset(0, 1).Value
    End If
    Set c = Nothing
  End If
End Function

 

[JazzyLee]

I would much prefer to run this with the INVALID_CC_LIST workbook unopened but if I comment out this statement

Workbooks.Open (INVALID_CC_PATH + INVALID_CC_LIST)

I get a "subscript out of range" error on the next line. I tried substituting a combination
e.g: Set wkbCCList = Workbooks(INVALID_CC_PATH + INVALID_CC_LIST)
so it will know where on the disk the workbook is but I also got an error. What am I missing?

 

[fredericofonseca]

try
dim sfilename as string
sFilename = INVALID_CC_PATH & INVALID_CC_LIST
excel.Workbooks.Open sFilename

Set wkbCCList = Excel.Workbooks(INVALID_CC_LIST)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Zathras]

JazzyLee: I don't think that's possible. AFIK you have to have the other workbook open for what you are trying to do.

Frederico: I'm not sure how that is any different in effect from what I posted. You are still opening the INVALID_CC_LIST workbook.

 

[JazzyLee]

Zathras,
I cannot express my appreciation (and elation) in getting this to work. "Mega Thanks" for the code. I would have never figured this out and it works perfectly. I guess I can pack up and go home now. The rest of the code in this macro will be easy after this challenge.