Can't vlookup or hlookup to a pivot table for one item. WHY?

[laxster]

Hi everyone,

I'm having some issues doing a vlookup and hlookup on a particular page of a spreadsheet. All the pages are pretty much the same, just looking up different departments (Primary, NS, NRCC, 110). The issue is with 110 - it seems to refuse to pull up the data on the 110 Summary sheet from the Pivot Table tab. I've tried using ISNA, with no results. I'm not sure what else to do - it seems like it should be working perfectly.

I'd attach the spreadsheet, but I am unable to pare it down enough without it losing major functionality. If needed, I could e-mail it to someone (it's not large, just not the 785KB max here)

Any thoughts? I am stumped.

 

[Kiwiman]

Howzit

Some of the things you can look at are:

1. Ensure the lookup value and the value in the left hand most column of the table are the same field type - i.e. both text or numbers etc
   1. Alternatively you may need to put a function in the lookup_value to make it the correct data type
2. The lookup value and the value in the lefthand most column has to be exactly the same
   1. if the left hand most column in table has empty spaces after the value, then your lookup_value has to have them as well

If this doesn't work, and you can't post a sample spreadsheet, send me an IM and I'll give you my email address - to send the file to.

 

[Kiwiman]

Howzit

Formula in question:

=HLOOKUP(E$15,'PVT Table'!$A$11:$D$52,2)

2 problems that I saw:

• The lookup_value was a number, but the value in the table was a text
  • Solution: Turn the lookup_value into text by using the Text function
• Range_Lookup was omitted (Excel treats as TRUE) from the HLookup, which requires the top row to be sorted in ascending order, to find the closest match. The table\array was not in ascending order
  • Solution: Use FALSE to find an exact match

My proposed formula

=HLOOKUP([B]TEXT(E$15,"###")[/B],'PVT Table'!$A$11:$D$52,2[B],FALSE[/B])