Nested if vlookup

[xrprrp]

Hi,

I am having a problem writing a nested IF with a VLOOKUP.

I have Columns D to O as my table array, with a text string in Col D of which I need to look up "leisure" "business" or "personal".

I then need the result of the formula to put which ever of these text options that appear in the text string in Col D in Col P.

Any help will be greatly appreciated!

 

[Special-K99]

=iferror(vlookup(...),iferror(vlookup(...),iferror(vlookup(...),"DATA NOT FOUND")))

 

[xrprrp]

returns data not found?

 

[Special-K99]

Then its not found.
What makes you think the data is there?

What is your formula?

 

[xrprrp]

In Col D it has a text string like

PD~BusinessDubrovnikLaneDE_CH~DISPLAY_PB_FM~BAN_DT~CROSS_AS~DCMJIVOX_IT~POE_DA~SDBM_DS~MLT_SS~_TG~BHV_TS~PRO_SZ~160X600_AD~SSCLICK_RT~DCPM_VV~VGPMX70IAS

<colgroup><col width="1415" style="width: 1061pt;"></colgroup><tbody>
</tbody>

I need to extract whether it says "Business" "Leisure" "Couples" "Family" from this text string and depending on which one have that entered into col P

I tried =IF(VLOOKUP("leisure",D:O,1,TRUE)="leisure",leisure,IF(VLOOKUP

and repeating it for each word but didn't work?

 

[Special-K99]

Well, no that wont work, since you never said the tables contain other data and that the string to lookup is only PART of that data.
You cant use VLOOKUP like that, you were the one that selected VLOOKUP.

Will try and find another way...

 

[xrprrp]

thank you

 

[Fluff]

Are you trying to check row by row? ie if D2 has Leisure then P2 has leisure

 

[jtakw]

Hi,

You can do it 2 ways.

1. "hard code" the table in the formula, as shown in P1.
2. Create a Table with the LOOKUP values, probably a better way to go, since you can easily change the table values, as shown in Q1.

Either formula copied down.

Book1
	D	P	Q	R	S	T
1	PD~BusinessDubrovnikLaneDE_CH~DISPLAY_PB_	Business	Business		Table	Business
2	PD~FamilyDubrovnikLaneDE_CH~DISPLAY_PB_	Family	Family			Leisure
3	PD~CouplesDubrovnikLaneDE_CH~DISPLAY_	Couples	Couples			Couples
4						Family
Sheet405

Cell Formulas
Range	Formula
P1	=LOOKUP(2,1/SEARCH({"Business","Leisure","Couples","Family"},D1),{"Business","Leisure","Couples","Family"})
Q1	=LOOKUP(2,1/SEARCH(T$1:T$4,D1),T$1:T$4)

EDIT: I've shortened your Text strings for my sample above, as it wasn't showing properly due to it's length.