looking up dilemma

[JohnE]

I will try an keep this simple. I have 2 worksheets
(Sheet1 and Sheet2) that are in use. On Sheet1 in columnA
are client names place there thru the validation method.
The names come from Sheet2. Also on Sheet1 in columnB are
services provided placed thru the validation method.
These also come from Sheet2. What I need to do is in
Sheet1/columnD is to place the pricing for the client and
service that is listed in Sheet2. Sheet2 is 12 columns
with the client in the first column and the remaining 11
columns with the pricing for each client with for the
different services, which the names of are listed in the
column headers. The clients are listed in alpha order on
Sheet2. They will be in working order on Sheet1.

Here is an example of current formula that is listed for
it. Naturally, it doesn't work.

=IF(B4="","",PRODUCT(HLOOKUP($B4,$AA$1:$AL$145,29,0),1/10))

The pricing is dependent for the client based on the
client (Sheet1/ColumnA) and service (Sheet1/ColumnB). Is
there anyone that can see the error of my way?
Thanks in advance for any assistance.
*** John

 

[RagDyer]

Try This:

=INDEX(Sheet2!$AA$1:$AL$145,MATCH(A4,Sheet2!$AA$1:$AA$145,0),MATCH(B4,Sheet2
!$AA$1:$AL$1,0))

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I will try an keep this simple. I have 2 worksheets
(Sheet1 and Sheet2) that are in use. On Sheet1 in columnA
are client names place there thru the validation method.
The names come from Sheet2. Also on Sheet1 in columnB are
services provided placed thru the validation method.
These also come from Sheet2. What I need to do is in
Sheet1/columnD is to place the pricing for the client and
service that is listed in Sheet2. Sheet2 is 12 columns
with the client in the first column and the remaining 11
columns with the pricing for each client with for the
different services, which the names of are listed in the
column headers. The clients are listed in alpha order on
Sheet2. They will be in working order on Sheet1.

Here is an example of current formula that is listed for
it. Naturally, it doesn't work.

=IF(B4="","",PRODUCT(HLOOKUP($B4,$AA$1:$AL$145,29,0),1/10))

The pricing is dependent for the client based on the
client (Sheet1/ColumnA) and service (Sheet1/ColumnB). Is
there anyone that can see the error of my way?
Thanks in advance for any assistance.
*** John

 

[Max]

One way ..

In Sheet1
-------------
with col A = client names, col B = services

Try in say, D4:

=IF(OR(ISNA(MATCH(A4,Sheet2!$AA$1:$AA$145,0)),ISNA(MATCH(B4,Sheet2!$AA$1:$AL
$1,0))),"",OFFSET(Sheet2!$AA$1,MATCH(A4,Sheet2!$AA$1:$AA$145,0)-1,MATCH(B4,S
heet2!$AA$1:$AL$1,0)-1))

(Pricing table is assumed located in Sheet2!$AA$1:$AL$145)

Format D4 as currency
Copy D4 down

Col D will return the price from the pricing table in Sheet2
for the client and the service listed in cols A and B

Unmatched client names or services will return blanks ""

 

[JohnE]

To Max and RagDyer, thanks to you both. Both versions
worked just like I needed.
*** John

 

[Max]

Always great to hear that it worked, John !
Thanks for the feedback ..