vlookup using cell description

[rfinnegan]

Hi All:

I have a spreadsheet populated with data from a data cube. Unfortunately, several of the items I want to do vlookups on don't have unique names and are not always in the same location each day.

For example, I have 2 categories: Men's Shoes and Women's Shoes that each have sub categories - dress, athletic, sandal. The layout, starting in cell A5 and ending in cell A12 looks like this"

Mens
-Dress
-Athletic
-Sandal
Womens
-Dress
-Athletic
-Sandal



If I wanted to do a vlookup on athletic, I can't do it on just the word "athletic", because 1) the name isn't unique, 2) it's not in the same cell every week, 3) it may or may not be the only "athletic" shoe in the data that week.

However, when I put my mouse on the Men's Athletic Shoe title in column A, a gray box pops up and shows me "Row: Shoes - Mens - Athletic". Is there a way I can do a vlookup on what is in the gray box?

thanks in advance.

 

[SirJB7]

Hi, rfinnegan!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

 

[Debraj]

Hi !

Welcome to the forum..

• Grey Box.. hmmmm.. Pivot Table... yep
• Its hard to imagine the structure.. still here is a sample try..

If your Pivot Table > Option > Generate GetPivotData is enabled then you can simply use formula as

=GETPIVOTDATA("ValueFieldName",$a$3,"RowLabel1","Shoes","RowLabel2","Men","RowLable3","Athletic")

$a$3 is the starting point of the pivot table.
and instead of "Shoes" / "Men", you can use Cell Reference like E3,F3

=GETPIVOTDATA("ValueFieldName",$a$3,"RowLabel1",E3,"RowLabel2",F3,"RowLable3",G3)

BTW, if you are worried regarding RowLabel1, RowLabel2 or RowLabel3
Pivot table is kind enough to produce the formula for you... just you have to help him, to set the FIXED TEXT reference to CELL Reference. Try it.. its so simple.

 

[rfinnegan]

Here's a sample of the data I'm working with (only 3 weeks worth of data rather than the full 5). As you can see, there are several sub categories that share the same names with other sub categories. Also, you'll notice there are fewer rows of data in week 2 than week 1 which means I can't use an absolute cell location to pull the data, as it may be in a different location from one week to the next.

The data is from a data cube - the source is external, so I had to break the link before I could post it. That said, these tabs of data are pivot tables when I'm working on them.

Thanks again for looking.

 

[Somendra Misra]

Hi rfinnegan,

See the attached file (yellow highlighted cell). This is Just an idea based on my understanding of your problem. If this is correct the formula can be extended for the entire table.

Regards,