Two-column lookup help!

  • Thread starter Thread starter EKnight
  • Start date Start date
E

EKnight

Greetings,

I am having difficulty looking up an item using two-column lookup
formulas as suggested. Following are the parameters of my situation

2 Different Workbooks

Workbook 1 (Reference Workbook)

Column b c d
125 Lay Foundation March 9, 2004
125 Purchase Frame mat. March 27, 2004
125 House complete May 15, 2004
267 Lay Foundation June 5, 2004
267 Purchase Frame mat. June 28, 2004
267 House complete August 29, 2004

Workbook 2 (Extracted information)

Column b c d
ID # Date of completion for Process A Date of comp. for
Process B etc. etc.

Up to this point, I have tried many variations of the following
formula to make things work (assuming ID# is in row B1):
=INDEX(WORKBOOK 1 D:D, MATCH(B1&"Lay Foundation", Workbook 1
B:B&Workbook 1 C:C,0))

This was also of course done with Ctrl, Shift, Enter (array formula)

My question is simply how can I solve this? I get back the error
message #NUM everytime. I am quite fearful that two-column lookups
might not be possible when referencing a second workbook.

Would be greatly appreciative for any help I could receive!

Thanks,

Eric
 
When I have done this kind of thing before I have used a helper column to concatenate the data from the two key columns. Insert a new column B on your sheet1 containing the data to be looked up. Enter in B1: C1&" "&D1. This should provide a result like "125 Lay Foundation". Then you can do a VLOOKUP on your results page like:

=VLOOKUP($B1&" Lay Foundation", Sheet1!$B$1:$B$100,4,FALSE)

In the first argument ($B1&" Lay Foundation") you are reconstructing the concatenated data that your lookup table is keyed on. The second argument (Sheet1!$B$1:$B$100) is the range reference for your lookup table. The third argument (4) says return the data in the fourth column of the lookup range, and the fifth argument (FALSE) says look for an exact match.

If you head up your columns with the Process name, you can change the first argument to something like $B1&" "&A$1, which is a formula you can use to fill the entire output range.
 
Back
Top