Bernie, I think I know where the confusion is.. Hear me out...
I need to find the keyword (in table) inside the text in E21 first.

E21 (or in the whole E column values) may say something like: "AirFare
transactions" or "Direct Travel" or "other non-travel"(which is not travel,
but that's another story...

It would look something like: (of course this won't work...)

=IF(ColumnEitem<"",VLOOKUP(AllKeywordsinCcol,
ColumnEpivotItem,returnmyCategoryinDcol,False),"")

Should I post a new thread, and try to explain the situation better? Not
sure where it's ambiguous... except I'm not looking at exact matches, but
text (keyword) within larger text (in E21+column) so I can apply a category
that will update once the pivot table is updated.

Maybe 48 nested IF statements won't be so bad... How many can 2007 handle?

Thank you for your time,
Pilar

"Bernie Deitrick" wrote:

Bernie, thank you for your reply. Does this mean I need to create 48 (12X4)
VLOOKUPs nested in 12 IF functions? I'm not sure how to implement your
solution...

No. You would use one IF/VLOOKUP combination along the lines of

=IF(E21<"",VLOOKUP(E21,Table,2,False),"")

Where table is the address of your new table, like $H$1:$I$45

So, if "Per Diem" is in E21, the formula would return "3 Travel"

Note, that it is better if you type the formula in directly rather than click on the cell in the
pivot table to create a link: that will fire the GetPivotData, which doesn't copy well...

HTH,
Bernie
MS Excel MVP



I'm searching for the keywords on table, which are then found in the first
column of a pivot table.

Thank you for your help,
Pilar

"Bernie Deitrick" wrote:

Pilar,

How about different AND better.... Simply make a table with the unique keywords listed down a
column, and then categorize them:

Keywords Category
Per Diem 3 Travel
Diem Lodging 3 Travel
Diem 3 Travel
etc, etc

Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match)

HTH,
Bernie
MS Excel MVP


"Pilar Morales" wrote in message
...
Greetings,

I have a pivot table where the first column holds the string to search
(first value in E21)


Airfare Account
AirFare Transactions
Car Rental
Meals & Incidentals



I have a table of 12 categories with about 3 keywords each:

Pick List: Keywords:
D E F G
3 Travel - Per Diem Diem lodging diem
4 Travel - Rental Car car mileage Ground trans
5 Travel - Airfare Air air Fare
6 Travel - Other travel


D21 (cell to the left of pivot table value) should automatically categorize
the item. Find is case sensitive, Search worked better...

This is what I have on D21:
=IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next
keywords"),$D$14)
OR- Doesn't work on multiple search or find. Not sure how to do it in VBA.
Brain freeze Help..please :) Different/faster/better solutions also welcome.

Thanks,
Pilar