Excel 2010 Pivot Tables and LOOKUP, VLOOKUP, INDEX/MATCH trouble

[mjbalys]

I have a pivot table in excel that I am using to find two values, one on each side of a maximum value (gaussian in shape). One pivot table is unsorted and the LOOKUP function finds the value I expect. To find the other value, I have a second pivot table sorted "Z to A" and then perform the same LOOKUP function. When sorted, it will not find the correct value even though I can see it in the list of values. If I unsort the pivot table, it works fine. Why would the function only work on unsorted data and not sorted? Is there a function I can use in its place? I've tried VLOOKUP and INDEX/MATCH and they fail the same way.

[JosephP]

LOOKUP requires the data to be sorted ascending. if your data is sorted descending index and match will work as long as you use -1 as the final MATCH argument

[mjbalys]

JP-

I tried making the last argument -1, and now the forumla comes up N/A. It will not evaluate the MATCH function.

[JosephP]

kinda hard to fix a formula I can't see ;-)

[mjbalys]

Sorry, I couldn't attach it on the forum and just realized how I could get it to you. I sent you a PM with the link and some details about the spreadsheet. Thanks for your help with this, it's been killing me for two days and I feel very dumb not seeing what I've done wrong.

[JosephP]

Your value column is the one your match formula looks at and that is not sorted descending

[mjbalys]

I thought I had the pivot table sorted. I guess I don't see what I need to do for the error to be corrected. Would you be able to explain?

[JosephP]

you have to sort the row field by the value field not by itself

[mjbalys]

To sort correctly, I can't just click on the row labels and tell it to sort descending? I'm sorry but trying to have it sort another way isn't sticking out to me. Where should I be looking? Or I guess what cell should I be guiding myself toward to have the sort work correctly for the formula?

[JosephP]

right-click the row field, sort-more sort options-then choose to sort descending by the value field

[mjbalys]

When I right click on the Row Labels field, I don't have any options to sort like you speak of. I'm sorry but I am clearly missing what you are describing

[JosephP]

not the cell with 'row labels' in it-right-click one of the actual row field items such as the cell below that

[mjbalys]

Screen shot 2013-04-19 at 4.50.01 PM.png
When I right-click on an actual row value, I do not have any options for sorting. The Data...Sort... menu option also has nothing that I can see would be helpful.

[JosephP]

your thread title says you are using 2010-that screenshot is from 2011 where the option is not present

[mjbalys]

I've been bouncing between my Mac and PC - let me go check there. Sorry for the confusion.

When I right click on a row, Sort > Sort More Options... is greyed out.

[JosephP]

works for me in your sample workbook