Vlookup with MATCH? Better than nested Vlookups?

[trillium]

Hi there
I'm trying to find the best way to lookup values off a pivot table, however the required tag in the first column only appears once. I need to tell the formula that when it sees the tag "Q1" for example to then lookup the info starting from the column next. That's fine for the first row's formula, but the second to forth need to say, Once the tag "Q1" is found (wherever that is), lookup starting from 1 row below, or 2 rows below etc.

Or maybe I should be using offset?MATCH help.xlsx

I have attached the example as I'm sure it's easier to understand visually!

[Ace_XL]

On the pivot table itself do this

Right click on the pivot table in the 'Quarter' column
Filed Settings -- layout and printout -- 'Check' the repeat item labels box

You now have Q1, Q2 etc. all the way down in the first column and can use a SUMIFS to populate desired values

Also, you'd be better of having to repeat the Q1, Q2 etc. entries in the finished table

[jason.b75]

Just to be sure I'm on the right wavelength, try this formula in L7 and fill down.

=INDEX($C:$C,MATCH(LOOKUP("zzz",K$7:K7),$A:$A,0)-MATCH("zzz",K$7:K7)+ROWS(K$7:K7))

Does that produce the expected output?

[trillium]

Hi! YES IT DOES!!! Thanks for your help!

I have a question for you though, the "zzz" - how did that work and not return an error since I was looking for either Q1 or Q2...??

[jason.b75]

If you look after the range in MATCH you will see that the FALSE argument for an exact match has been omitted, so it looks for the last value less than or equal to "zzz" in the specified range.

"zzz" only works with text though, if you're working with numbers you need a really big number, such as 1E+100 (which is 101 digits, so much bigger than any real world number).

edit:-

I forgot how I wrote part of my own formula, that was for the second MATCH function that finds the relevant quarter in column K

In the first match, LOOKUP does the same as above, then passes the result it finds in column K (Q1, Q2, etc) to MATCH for an exact search in column A

[trillium]

WOW this is amazing and very powerful. Thank you so much.
How would I amend the formula if I had a specific item I wanted to return the value of for each quarter? I.e. Attribute # C1.A3.1 for Q1, then Q2? I still need to know where to start and stop to start then looking for the row where C1.A3.1 is located? (This time to return total count in column F).

I could do it from the raw dataset itself with a long countifs statements, but want to learn more about how to use the INDEX/MATCH function.

[jason.b75]

If you're ready to go a bit more advanced, then we can try a few new tricks.

One thing that a lot of people don't realise is that index actually returns a range rather than value, and you then see the value from that range as the result of the formula, so =INDEX(A:A,123) is the same as =A123

With this in mind, one trick you can use to find a range within a range is =INDEX(A:A,20):INDEX(D:D,50) which would be the same as using =A20:D50 a range that you could use as part of another formula.

Going back to your task, I noticed in the sample that column A had Q1 at the top and Q1 Total at the bottom of the Q1 range, using the method above, you could use this to your advantage and match "Q1" in the first INDEX with an exact match, and "Q1 Z" in the second INDEX with an approximate match (notice that in an alphanumeric sequence, "Q1 Z" would be greater than "Q1" and "Q1 T?????", but less than "Q2", so would find the correct end point.

The ranges produced by this could then be used in VLOOKUP, in the form of =VLOOKUP(attribute#, INDEX(B:B,MATCH("Q1",A:A,FALSE)):INDEX(C:C,MATCH("Q1 Z",A:A)),2,FALSE)

Hopefully that makes enough sense for you to start playing with things, but if you need help, don't be afraid to ask.

One last tip, as it will only be 4 variables, (Q1, Q2, Q3 and Q4), you could enter the =INDEX():INDEX() part of each into the Refers to: box of a named range and create 4 named ranges, 1 for each quarter, which would make the formula easier to manage.

Hope this is of use.

Jason.

[trillium]

WOW - That is so cool! I have learned so much today. Yes, I joke with my friends and co-workers I have an Excel geek! Which is why I end up doing a lot of reporting for our area. INDEX and MATCH for some reason intimidated me, but now I'm feeling much more comfortable! And I didn't know that about A:A,123 is "same as" A123 - Light bulb moment

The sample dataset I sent you is a copy of a pivot table, which is dynamic so the rows and positions of the items we are looking up will be changing every month, which was why I needed a way to keep the formulas dynamic. That table I used your formulas for feed charts that can be updated by now just selecting the region/team the manger wants to see!

Thank you again for you patience and teaching me. I hope one day I can be knowledgeable enough to help others on here!
Happy Holiday!
From Snowy Ottawa, Canada

[trillium]

Thanks! I didn't know that either! Not sure how I missed your reply before. Sorry :-)

[jason.b75]

I hope one day I can be knowledgeable enough to help others on here!

I find knowledge is best gained through practice. Make copies of workbooks before trying something new so that you don't risk critical data when it goes wrong. I find real data is always better to work with than random test data, just be sure you always have a backup copy or 2.

I usually have a minimum 3 copies, the original, the one I'm working on, and a last known good copy (or several of those at different stages if it's something fairly big). That way, when things go wrong, you only have to work back until you find the cause, rather than starting from the beginning again.

Failure will probably teach you more than success.

As for intimidating INDEX MATCH combinations, http://www.excelforum.com/excel-gene...ml#post4176905