vlookup with datavalidation list

[nitika.manhas]

I created a pivot table
then I created a data validation list with the list of product category
now I am trying to use vlookup with TWO LOOK UP FIELDS along with datavalidation list
i am getting N/A result :-( any input?

 

[Chihiro]

You would either need helper column to left of Pivot Table (with concatenated value for A & B columns), or use Index/Match.

=INDEX(C$2:C$48,MATCH(P5&Q6,$A$2:$A$48&$B$2:$B$48,0))
Confirmed as array (CTRL + SHIFT + ENTER)

 

[GraH - Guido]

Try this...
Go in cell Q6, type = and select the corresponding coordinates from the pivot with the mouse. Confirm with enter. Then try to change some of the arguments with the references P5/Q5.

 

[nitika.manhas]

You would either need helper column to left of Pivot Table (with concatenated value for A & B columns), or use Index/Match.

=INDEX(C$2:C$48,MATCH(P5&Q6,$A$2:$A$48&$B$2:$B$48,0))
Confirmed as array (CTRL + SHIFT + ENTER)

-------------------------

i tried index with match but it still gave me error

 

[Chihiro]

Screen shot doesn't tell me much. Upload desensitized sample file. I suspect you either have trailing space(s) in one of the field, or did not use array formula.

 

[nitika.manhas]

thank you so much! it worked.

i missed the array part