Hlookup from a pivot I made - Second look up results in N/A

[Mikey mike]

Hi,

I created a pivot table with two row labels. I want to look up the first string of row labels and there after only the first 5 of the second string of data. I manage to to a Hlookup and got all my results but when I move on to the next row label I get N/A. See below example:

Pivot created that I am trying to hlookup from.

[TABLE="class: grid, width: 500, align: center"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:10422;width:214pt" width="285"> <col style="mso-width-source:userset;mso-width-alt:10349;width:212pt" width="283"> </colgroup><tbody>[TR]
[TD="width: 285, align: center"][/TD]
[TD="width: 285, align: center"]A
[/TD]
[TD="width: 283, align: center"]B
[/TD]
[/TR]
[TR]
[TD="width: 285, align: center"]1
[/TD]
[TD="width: 285"]Symtom
[/TD]
[TD="width: 283"]Count[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]symtom1[/TD]
[TD="align: right"]70
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]model1
[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]model2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]model3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]model4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]model5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]symtom2[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]model6
[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]model7
[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]model8
[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]model9
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]model10
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]model11
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD]model12
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]model13
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

Hlookup results

[TABLE="class: grid, width: 500, align: center"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:9984;width:205pt" width="273"> <col style="mso-width-source:userset;mso-width-alt:5485;width:113pt" width="150"> </colgroup><tbody>[TR]
[TD="width: 273"][/TD]
[TD="width: 273, align: center"]A
[/TD]
[TD="width: 150, align: center"]B
[/TD]
[/TR]
[TR]
[TD="width: 273, align: center"]1
[/TD]
[TD="width: 273"]symtom1
[/TD]
[TD="width: 150, align: right"]70
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]model1 (=HLOOKUP(symtom1,A2:A16,2,FALSE)
[/TD]
[TD="align: center"]3 =VLOOKUP(=HLOOKUP(symtom1,A2:A16,2,FALSE),A2:B16,2,FALSE)

[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]model2 (=HLOOKUP(symtom1,A2:A16,3,FALSE)
[/TD]
[TD="align: center"]2 =VLOOKUP(=HLOOKUP(symtom1,A2:A16,3,FALSE),A2:B16,2,FALSE)
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]model3 (=HLOOKUP(symtom1,A2:A16,4,FALSE)
[/TD]
[TD="align: center"]2 =VLOOKUP(=HLOOKUP(symtom1,A2:A16,3,FALSE),A2:B16,2,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:9984;width:205pt" width="273"> <col style="mso-width-source:userset;mso-width-alt:5485;width:113pt" width="150"> </colgroup><tbody>[TR]
[TD="width: 273, align: center"]5
[/TD]
[TD="width: 273"]symtom2
[/TD]
[TD="width: 150, align: right"]40[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]#N/A
(=HLOOKUP(symtom2,A2:A16,2,FALSE)

[/TD]
[TD="align: center"]#N/A =VLOOKUP(=HLOOKUP(symtom2,A2:A16,3,FALSE),A2:B16,2,FALSE)
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]#N/A (=HLOOKUP(symtom2,A2:A16,3,FALSE)
[/TD]
[TD="align: center"]#N/A =VLOOKUP(=HLOOKUP(symtom2,A2:A163,FALSE),A2:B16,2,FALSE)
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]#N/A (=HLOOKUP(A3,A2:A16,4,FALSE)
[/TD]
[TD="align: center"]#N/A =VLOOKUP(=HLOOKUP(symtom2,A2:A16,3,FALSE),A2:B16,2,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]
In other words when I do an Hlookup for symtom1 I excel picks up symtom1 as a reference and I can then get the models underneath it. But with symtom 2 if does not find it and my result is N/A and it can find symtom 2. I double checked spelling, checked the type. Its exactly the same as symtom1 yet symtom2 cannot be found by the hlookup.

Hope this make sense and someone can exaplin why my Hlook up works only for the first refernece but not the second.

Thank you

 

[wfm007]

You have to change the range for 'symtom2':

=[COLOR=#333333]HLOOKUP(symtom2,A8:A16,2,FALSE)
[/COLOR][COLOR=#333333]=VLOOKUP(=HLOOKUP(symtom2,A2:A16,3,FALSE),A2:B16,2,FALSE)[/COLOR]

***This is an manual task....you can use other formulas to avoid manual work

 

[Mikey mike]

Thank you WFM,
adjusting the range worked. I dont understand why it works when the range I have originally includes symtom 1 and symtom 2. Infact I named that whole columb range and used that. My problem is when the original data from the pivot table is adjusted and then that range would not match.