Excel, lookup value in array based on two column values 3

[wbow]

Hi,
I am using Excel 2000 and have two worksheets in the workbook, sheet1=Calc, sheet2=LookupList.
The lookup list in sheet2 is 9 columns wide by 33 rows deep.
Each row contains a job description, each column contains the associated cost based on a service level.

I am trying to lookup a cost value in sheet2 based on two non-adjacent columns in sheet1, Job and Service Level.
I have tried DGET, Vlookup, Index/Match but have been unable to resolve this issue, any help greatly
appreciated.

 

[lionelhill]

If your service levels themselves have names such as "Gold", "Silver", make a table to convert these into numerical values (with a simple vlookup), corresponding to the column number of that service level on sheet 2.

Now use Vlookup as normal, searching for the job description, and using the numerical version of the service level as the column to return.

 

[AnotherHiggins]

What you ask for is certainly possible. And I'll provide a direct answer. But first, please, please, please do yourself a favor and read the following first:

You probably don't want to hear this, but your data table is structured 'wrong'. You will make your life easier in the long run if you Normalize the table. That would give you a table that looks something like this:
[tt]
JobDesc SL Cost
abc 1 $10
abc 2 $15
abc 3 $20
...
xyz 1 $100
xyz 2 $125
xyz 3 $150[/tt]

Having your data in this type of structure will make reporting, analyzing and summarizing much, much easier.

But you don't want to take the time to manually recreate the table, do you? Of course not! Well, there's good news. Skip's awesome FAQ, faq68-5287 will walk you through using a Pivot Table to Normalize your data in just a few seconds (maybe upwards of two minutes if you're not familiar with Pivot Tables).

Seriously, that is the way to go.

Now, as promised, here's a solution for how your data is currently structured....

You're on the right track with Index/Match. You'll just use Match for both the Row and Column sections of the formula. Assuming that you have a Header Row and a Header Column, it will look something like this:
[tab]=INDEX(LookupList!A1:J34, MATCH(A1,LookupList!A1:A34), MATCH(B1,LookupList!A1:J1))
(where Calc!A1 = JobDesc and Calc!B1 = ServiceLevel.

But, seriously, consider the Normalized Table structure. Even if you don't use it this time, you should design future tables this way. Trust someone who has learned the hard way.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wbow]

Hi lionelhill & anotherhiggins,
Many thanks, both work equally fine, however, will adopt the normalised table approach in future.

 

[lionelhill]

Thanks for pointing to Skip's FAQ. Jolly useful.