Two column value match table look up
 

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.

Two column value match table look up
 

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.

Two column value match table look up
 

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
---

Two column value match table look up
 

=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.

Two column value match table look up
 

"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
---