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.

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John" wrote in message
...
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.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Think the double Index is redundant:

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

Biff

"Max" wrote in message
...
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John" wrote in message
...
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.

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

Yes, of course. Thanks for the refinement !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---