Vlookup

[bluenoser13]

Hello:

I am a newbie to Excel and tis list. I am trying to obtain values fro
the second sheet afer putting criteria from teh first sheet. Here i
teh first sheet:

Sales Results - All Regions

Month -------- (Enter a month number)
Product ---------- (Enter a Product number)
Units Sold ----------- (Result from second sheet)

Products Month
1=Refrigerators 1=Jan 6=Jun 11=Nov
2=Microwaves 2=Feb 7=Jul 12=Dec
3=Ovens 3=Mar 8=Aug 13=Total
4=Dishwashers 4=Apr 9=Sep
5=All products 5=May 10=Oct

Here is a sample of the data from the second sheet:

All Regions
Units Sold
Product ID Jan Feb Mar Apr May Jun
1 1,225 1,074 1,199 1,003 1,157 1,271
2 1,852 1,648 1,670 1,793 1,853 1,963
3 502 562 472 607 570 557
4 578 581 571 584 589 563
5 4,157 3,865 3,912 3,987 4,169 4,354

1=Refrigerators
2=Microwaves
3=Ovens
4=Dishwashers
5=All products

I would like to have these values in the units sold section on th
first sheet. Your assistance is greatly appreciated. :confused

 

[Domenic]

Assuming that Sheet 2 contains your data, and that your data starts in
the second row, enter the following formulas in Sheet 1...

C2:

=IF(A2<>13,INDEX(Sheet2!B2:G6,MATCH(Sheet1!B2,Sheet2!A2:A6,0),MATCH(CHOOSE(Sheet1!A2,"Jan","Feb","Mar","Apr","May","Jun"),Sheet2!B1:G1,0)),"")

D2:

=IF(A2=13,SUM(INDEX(Sheet2!B2:G6,MATCH(Sheet1!B2,Sheet2!A2:A6,0),0)),"")

...where Sheet1!A2 contains the month number and Sheet1!B2 contains the
product number. You can complete the months in your data, make the
necessary adjustment for the range, and add the remaining months in the
formula, if and when neccessary.

Hope this helps!

 

[Guest]

Use the INDEX Function.
And, it's best to use Names in this type of solution using the INDEX
function.

First, create a Name for your Units Sold table:
- Select the cells containing values in your Units Sold table.
(Do not select the Months or Product IDs. In your example, select the 30
cells - from 1,225 to 4,354. In a table w/ 4 products + all products & 12
months + total, it will be 65 cells.)
- Insert menu
- Select Name
- Select Define
- Type the following:
"tblUnitsSold"

As you use Excel more, you'll learn to appreciate Names. You can also use
the Name Box for this process of creating a Name. It's faster, & it is
located to the left of the Formula Bar. Select the (30 or 65) table values,
then type the Name in that Name Box.

Next, assuming the labels, "Month", "Product", & "Units Sold" are in cells
A1, A2, & A3.
In the 1st sheet:
Cell B1: (Enter a month number)
Cell B2: (Enter a Product number)
Cell B3: (Result from second sheet)

In B3 type this formula:
=INDEX(tblUnitsSold,B2,B1)

If you don't want to mess with the Name, the formula is:
=INDEX(Sheet2!B4:G8,B2,B1)

After this, take a look at the MATCH function. Used in conjunction with the
INDEX function, they will enable you to type a month or product name in
cells B1 & B2.

Good luck.

 

[Domenic]

Assuming that Sheet 2 contains your data, and that your data starts i
the second row, enter the following formulas in Sheet 1...

C2:

=IF(A2<>13,INDEX(Sheet2!B2:G6,MATCH(Sheet1!B2,Sheet2!A2:A6,0),MATCH(CHOOSE(Sheet1!A2,"Jan","Feb","Mar","Apr","May","Jun"),Sheet2!B1:G1,0)),"")

D2:

=IF(A2=13,SUM(INDEX(Sheet2!B2:G6,MATCH(Sheet1!B2,Sheet2!A2:A6,0),0)),"")

...where Sheet1!A2 contains the month number and Sheet1!B2 contains th
product number. You can complete the months in your data, make th
necessary adjustment for the range, and add the remaining months in th
formula, if and when neccessary.

Hope this helps!

After reading Robert's post, I realized there was an easier way.
Replace my formulas with the following...

C2:

=IF(A2<>13,INDEX(Sheet2!B2:G6,B2,A2),"")

D2:

=IF(A2=13,SUM(INDEX(Sheet2!B2:G6,B2,0)),""