Excel lookup query

  ElephantRhino 16:39 08 Jun 06
Locked

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 06

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 06

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 06

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 06

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

click here

  ElephantRhino 12:20 09 Jun 06

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…