Excel lookup query

  ElephantRhino 16:39 08 Jun 2006

I'm creating a Timesheet where Customer and Projects are entered by the users via data validation (thanks previously to VoG) and from the text entries I want to lookup codes for entry into the billing system. Customer is no problem - standard vlookup table. Projects is another story. In order to generate a hierarchical data validation, i.e only select projects available to that customer, I have the following lookup tables:

1 - Customer, Customer Code - simple columnar table
2 - Projects by Customer - Customer along the top, with each project below the customer. At the right hand side of this table is the Project Code.

When looking up the project code from the customer name, I need to identify the column (customer) and then the row (project) and from the row identifies the code, which is appended to the Customer/Project code to make a unique ref.

I thought Match would do it but have had no joy. I hope my explanation is clear and welcome any advice.

  VoG II 16:47 08 Jun 2006

MATCH should work. Make sure you set the third parameter to 0 to go for an exact match:

=MATCH(what, range, 0)

  ElephantRhino 09:30 09 Jun 2006

I tried 0 parameter - just getting #N/A.

My table is:

Cust1 Cust2 etc ProjCode
Proj11 Proj12 ... 0
Proj21 Proj22 ... 1

so I'm trying to pick the code by matching Proj##

  ElephantRhino 10:17 09 Jun 2006

I can only get Match to work if I limit the lookup array to a single column! Unfortunately I'm not going to know which column so I'm stumped!

  VoG II 11:22 09 Jun 2006

This is a trifle unwieldy but it works. You may be able to neaten it up:

click here

  ElephantRhino 12:20 09 Jun 2006

I didn't completely follow your example but it did set me off down a different track - if I use Cust names as the range names for each column of Projects, making sure that they're spelt correctly, then I can use Indirect(CustName) as the array to match the Project Name against and this seems to be working!

Many thanks for your help.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone