Multiple Offset in Vlookup

[anappleuser]

Hello,

I am using a nested formula to get data from an excel sheet. The Formula looks like below (just an example)

=VLOOKUP($H3,OFFSET($A$1,MATCH(I$2,$A:$H,0),1,17),3,0)

Now what happens sometime I have to move more step than defined in above formula. If You see the example table below you will get idea. Assume that i have multiple tables in one sheet and only Table title is unique(i.e. Table 1, Table 2) If I want to get % data of Feb 2017 from Type4, Subtype4 and category3. How can i modify above formula to achieve my answer?
See example in google sheets. (however i need solution in ms excel only)https://docs.google.com/spreadsheets/d/1G7Le8VVOnGorWrrW4HFbM6W9H25QiGTmaza6IE0n3W4/edit?usp=sharing

 

[L. Howard]

With some modification of the sheet example I am able to use a formula like this to do what I think you want.

=IFERROR(INDEX($H$5:$L$20,MATCH($E$2,$A$5:$A$20,0),MATCH($F$2,$H$3:$L$3,0)),"")

Which requires the use of column A to house a combined MATCH value that looks like this. Or shortened as in the subset shown.

[TABLE="width: 167"]
<tbody>[TR]
[TD]Type1Subtype1Category1[/TD]
[/TR]
[TR]
[TD]Type1Subtype1Category2[/TD]
[/TR]
[TR]
[TD]Type1Subtype1Category3[/TD]
[/TR]
[TR]
[TD]Type1Subtype1Category4

[TABLE="width: 167"]
<tbody>[TR]
[TD]T1S1C1[/TD]
[/TR]
[TR]
[TD]T1S1C2[/TD]
[/TR]
[TR]
[TD]T1S1C3[/TD]
[/TR]
[TR]
[TD]T1S1C4[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

I have a drop down cell for the dates to be referred to by the formula and the INDEX is the columns and rows of the pertinent data holding the percentages.

Then with three cells to enter the 1, 2, 3, 4's for a TYPE, SUBTYPE & CATEGOTY, a lookup Match value like Type1Subtype1Category3 or T1S1C3 is produced.

Match that value with the date in the drop down and the intersect is the percentage sought.

If this may work for you, I can 'splain it best I can for the sheet set up and perhaps post a link to a working example.

However, it would require altering the data format a bit and getting rid of all the merged cells.

Howard

 

[anappleuser]

Hello Sir,

Thank you for sparing time for my problem. Well, I think unmerging table data every time wouldn't be a good idea as per my requirement. because in actual scenario, I get whole lot of tables in a single sheet. My purpose is to automate the work (not using a macro) by using formulas, So I can get directly my results into another formulated sheet as soon as i paste excel tables in my formulated file. VLOOKUP + OFFSET works like a charm unless it is about moving one step further.

Thanks again for all of your hard work Sir

 

[L. Howard]

Okay, but living with merged cells can cause more problems than they solve, as expressed by many of the pros/MVP's in the forum.

No vba used in the Index/Match, just a formula. I do not think you can make a VLOOKUP do the work you want, but perhaps I'm wrong.

Here is a link to a shortened and slightly modified data example to demo the index/match using a helper column.

Enter the Type, Subtype, Category in the cells C1,C2, C3.
Select a date in the drop down cell F2.
Read the % in the formula cell H2.

The column O & P stuff can bee way off screen.

Howard

https://www.dropbox.com/s/m99bjr8uq85zfos/TSC example.xlsm?dl=0