Which Method Vlookup or Index? 2

[DarkOne72]

Good Day All !
I have an excel question and not sure how to get it to work properly. I have tried both VLookup and Index but can't seem to get it to work. To give a brief description of whats going on here is a break down of what I am trying to do:

Sheet 1:

Column A   Column B   Column C    Column D    Column E
Year       Desc       Cost        %           Type (drop down list from columns in sheet2)
2016       test       12.00       5           Fabric

Sheet 2:

Column A   Column B   Column C    Column D    Column E
Year       Mulch      Rock        Fabric      Sand
2015       1          2            3           4
2016       3          4            5           6

So this may look confusing but it isn't really. I am trying to get the actual value of what is typed in on sheet 1 column a (Year) and also whats selected in column e (Type) and put the value in column d (%).
In sheet 2 the data is under all the columns, so if you typed in sheet 2016 and selected fabric it would give you 5 on sheet 1.
I have tried these two formulas:
=INDEX(Sheet2!A4:E29,MATCH(A2&E2,Sheet2!A4:E29,0))
or
=VLOOKUP(A2,Sheet2!A4:E29,FALSE)&VLOOKUP(E2,Sheet2!A4:J29,FALSE)

Can someone please assist?

Thank you!

 

[SkipVought]

Hi,

NEITHER!

Use the PivotTable Wizard.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DarkOne72]

Hi Skip,

I can't use a pivot table as I will be using the value to calculate another field in another sheet. I wish it was that easy, I know how to do a pivot table but it isn't practical in this situation.
Thanks!

 

[SkipVought]

Using "the value"

What value?

How can we help you if you fail to include all the constraint in you requirement and supply a adequate example?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DarkOne72]

I did include it in the information.
Like i stated before, basically on sheet 1 you would type in column a the year, and select from the drop-down list in column e. the formula i am needing help on will go in column d on sheet 1. Based on what you type in column a and pick from column e sheet 1 it will give the value from sheet 2 to go into column d on sheet 1

I hope i explained it better

 

[zelgar]

Try using the following code:
=OFFSET(Sheet2!A1,MATCH(A2,Sheet2!A:A)-1,MATCH(E2,Sheet2!1:1,0)-1)

 

[DarkOne72]

Zelgar,

I couldn't get it to work but I found a work around by doing a vlookup+match and naming the cells to make like a table and it I am now able to get the values to come in.

Thank you all for your assistance.!!!

 

[SkipVought]

Change the heading from % to Pct, in order to create Named Ranges for your data.

Select your entire table.

Formula > Defined Names > Create from Selection > Create names from data in TOP row.

Formula to AGGREGATE the data in your table by Year and specific Types.

Assuming that your Sheet 2 table is in row 1 and column A...
[tt]
B2: =SUMPRODUCT((Year=$A2)*(Type=B$1)*(Pct))
[/tt]
Copy across and down.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

In my formula, I had the data for Sheet 2 Starting in Cell A1, with Dates being in the A column, Mulch in B, ... Therefore, the data for Rock in 2016 was in cell C3. Basically, my formula figured out the cell coordinates of the data to retrieve. The formula could have been more restrictive for the data, currently it was looking at everything in the A column for finding year information & everything in Row 1 for the type of material. If you wanted it more restrictive, then the Sheet2!A:A and Sheet2!1:1 would needed to be restricted to more of a range versus the entire Column / Row.