Lookup Function Problems
 

I'm trying to set up a sheet which looks up information from another sheet.
Here is a very simplified example of what I'm trying to achieve:

Sheet1:
A B C D E
1 Code Desc Jan Feb Mar
2 AAA 10 10 15
3 BBB 15 15 20
4 CCC 20 20 25

Sheet2:
A B C D
1 Code Desc Month Price
2 BBB Feb
3 AAA Mar


Sheet 1 basically holds product information. Column A being product code, B
being product description and then C, D & E being prices for such products in
the corresponding months (this is in fact continued for all 12 months in the
actual sheet).

In sheet 2 a user will enter information in columns A & C and specifically
what I'm interested in here is lookup functions for columns B & D. Column B
currently has a VLOOKUP function to get the description text from Sheet1 when
the user enters the product code. The problem I have is I need a similar
function for column D to lookup the corresponding price when the month is
entered. This issue is I need it to match both the month and the product code
to return the correct price.

Can anyone hlp me with this? It may be that I'm approaching this the wrong
way so if thats the case I'm open to any better suggestions.

Thanks in advance.

Try this formula

=INDEX(Sheet1!$C$2:$N$20,MATCH(Sheet2!A2,Sheet1!$A $2:$A$20,0),MATCH(Sheet2!C
2,Sheet1!$C$1:$N$1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"FFW" wrote in message
...
I'm trying to set up a sheet which looks up information from another
sheet.
Here is a very simplified example of what I'm trying to achieve:

Sheet1:
A B C D E
1 Code Desc Jan Feb Mar
2 AAA 10 10 15
3 BBB 15 15 20
4 CCC 20 20 25

Sheet2:
A B C D
1 Code Desc Month Price
2 BBB Feb
3 AAA Mar


Sheet 1 basically holds product information. Column A being product code,
B
being product description and then C, D & E being prices for such products
in
the corresponding months (this is in fact continued for all 12 months in
the
actual sheet).

In sheet 2 a user will enter information in columns A & C and specifically
what I'm interested in here is lookup functions for columns B & D. Column
B
currently has a VLOOKUP function to get the description text from Sheet1
when
the user enters the product code. The problem I have is I need a similar
function for column D to lookup the corresponding price when the month is
entered. This issue is I need it to match both the month and the product
code
to return the correct price.

Can anyone hlp me with this? It may be that I'm approaching this the wrong
way so if thats the case I'm open to any better suggestions.

Thanks in advance.

Works a treat. Many thanks!

"Bob Phillips" wrote:

Try this formula

=INDEX(Sheet1!$C$2:$N$20,MATCH(Sheet2!A2,Sheet1!$A $2:$A$20,0),MATCH(Sheet2!C
2,Sheet1!$C$1:$N$1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"FFW" wrote in message
...
I'm trying to set up a sheet which looks up information from another
sheet.
Here is a very simplified example of what I'm trying to achieve:

Sheet1:
A B C D E
1 Code Desc Jan Feb Mar
2 AAA 10 10 15
3 BBB 15 15 20
4 CCC 20 20 25

Sheet2:
A B C D
1 Code Desc Month Price
2 BBB Feb
3 AAA Mar


Sheet 1 basically holds product information. Column A being product code,
B
being product description and then C, D & E being prices for such products
in
the corresponding months (this is in fact continued for all 12 months in
the
actual sheet).

In sheet 2 a user will enter information in columns A & C and specifically
what I'm interested in here is lookup functions for columns B & D. Column
B
currently has a VLOOKUP function to get the description text from Sheet1
when
the user enters the product code. The problem I have is I need a similar
function for column D to lookup the corresponding price when the month is
entered. This issue is I need it to match both the month and the product
code
to return the correct price.

Can anyone hlp me with this? It may be that I'm approaching this the wrong
way so if thats the case I'm open to any better suggestions.

Thanks in advance.