Two column value match table look up

[Guest]

I created a nifty database and report sheet workbook for my boss that
displays row fields from our large DBtable based on WorkOrderNunbers using

=VLOOKUP(WorkOrderNunber,DBTable,ColNumToDisplay).

Now he wants to have multiple "job" records for each work order. I could add
a JobNo column next to the WorkOrderNunber column in the database table, but
how can I do something like the above vlookup to search for lookup_value
matches against two columns instead of just one column?

Hope this problem description makes sense. Thx for your help.

 

[Max]

One way, perhaps something along these lines ..

Assuming A1:C1 contains the inputs for
work order #, job #, and col #

Put in the formula bar for D1,
and array-enter (press CTRL+SHIFT+ENTER):
=INDEX(INDEX(DBTable,,C1),MATCH(1,(WorkOrderNunber=A1)*(JobNo=B1),0))

 

[Max]

WorkOrderNunber, JobNo, DBTable
would be the defined/named ranges, eg:

DBTable =Sheet2!$A$1:$Z$100
WorkOrderNunber =Sheet2!$A$1:$A$100
JobNo =Sheet2!$B$1:$B$100

---

 

[Biff]

=INDEX(INDEX(DBTable,,C1),MATCH(1,(WorkOrderNunber=A1)*(JobNo=B1),0))

Think the double Index is redundant:

=INDEX(DBTable,MATCH(1,(WorkOrderNunber=A1)*(JobNo=B1),0),C1)

Biff

 

[Max]

Think the double Index is redundant:
=INDEX(DBTable,MATCH(1,(WorkOrderNunber=A1)*(JobNo=B1),0),C1)

Yes, of course. Thanks for the refinement !

---