Pivot on lookup formula results

**chetan.thakkar** · 04-15-2013, 11:11 AM

Greetings to all!

I am trying to run a pivot table with a column having results of vlookup formula.

The vlookup formula on the base spreadsheet is :

=LOOKUP(2^15,SEARCH({"Overdue","Red","PastY","Yellow","Green","Complete","No_BL"},$J2),{"01","02","03","04","05","06","07"})

1st objective is for excel to read a column (J in this case) and populate the respective source cell column (I) with the appropriate number (from 01 thro 07).

Subsequently, I am trying to run a pivot on this column (I) value (01 thro 07) to group the pivot table data.

Unfortunately, pivot output is not picking any value from the source table column I, apparently due to the source cell format.

If I manually convert the value in column I to the appropriate number (from 1 thro 7), pivot works fine. However, I would not prefer to reformat each record in column I source cell.

Any advise on this issue?

Thank you in advance..

**:) Sixthsense :)** · 04-16-2013, 05:45 AM

Try this...

Formula:

Please Login or Register  to view this content.

**chetan.thakkar** · 06-12-2013, 10:43 AM

Hi Sixthsense,

Thank you for the reply. Unfortunately, I am getting #N/A for some of the values (i.e. Green, Complete, No_BL). Not sure, why only some are giving this #N/A, whereas the remaining are showing some disconnect in the number mapping.

Any advise?

Thank you!