Convert different words to a specified value

[mjattkatt]

Hi,

I have a problem with converting a column of different page sizes("Full page", "Half page", "Quarter page" and so on) to the value of a full page(1, 0.5, 0.25 and so on).

I tried to wrap my head around Vlookup but haven't prevailed..

Attached find the example file. I will later put the data in a pivot table.

Thank you for your time and help!

[FlameRetired]

Hi mjattkatt,

Welcome to the forum.

Try this in J2. I assumed "Spread" as 2.

=LOOKUP(I2,{"Full page","Half page","Quarter page","Spread"},{1,0.5,0.25,2})

[mjattkatt]

Hi mjattkatt,

Welcome to the forum.

Try this in J2. I assumed "Spread" as 2.

=LOOKUP(I2,{"Full page","Half page","Quarter page","Spread"},{1,0.5,0.25,2})

Thank you for your time and quick help!

Tried the formula in the example file but just got an error in return.

[sandy666]

or

=CHOOSE(MATCH(I2,{"Spread","Full page","Half page","Quarter page","Eighth"},0),2,1,0.5,0.25,0.125)

[mjattkatt]

or

=CHOOSE(MATCH(I2,{"Spread","Full page","Half page","Quarter page","Eighth"},0),2,1,0.5,0.25,0.125)

Thank you Sandy666, no luck here either..

I feel like a complete idiot haha.. :D

Would you mind trying to enter it in the example file and uploading again?

Thank you so much for your help!

[sandy666]

Here is your example:

[mjattkatt]

Here is your example:

Wow, thank you Sandy666!

One last question, is it possible to wildcard the different page sizes.. Some posts have "Full spread" and "Whole spread", tried with * but it gave me no result in return.

Eternally greatful!

[sandy666]

Could you attach more descriptive example? with more possibilities what you want

[JohnTopley]

Try

=CHOOSE(MATCH(I2,{"Spread","Whole Spread","Full page","Half page","Quarter page","Eighth"},0),2,1,1,0.5,0.25,0.125)

As per Sandy's reply: need list of all possible descriptions.

[mjattkatt]

Hi again,

you can not believe how greatful i am for your time!!

Attached you find the new example file where the different page sizes have different names. The names can change each day as the user specify the name be themself, but "Full page", "Spread" and so on will always be in the name.

[Tony Valko]

On the Lookups sheet create the table like this:

Data Range

	A	B
1	Type	Value
2	Eighth	0.125
3	Full	1
4	Half	0.5
5	Quarter	0.25
6	Spread	2

Then, on the Data sheet enter this formula in J2 and copy down:

=LOOKUP(100,SEARCH(Lookups!A$2:A$6,I2),Lookups!B$2:B$6)

[sandy666]

you can try also (with all definitions inside):

=CHOOSE(MATCH(IF(IFERROR(SEARCH("Spread",I2)>0,0),"Spread",IF(IFERROR(SEARCH("Full",I2)>0,0),"Full",IF(IFERROR(SEARCH("Half",I2)>0,0),"Half",IF(IFERROR(SEARCH("Quarter",I2)>0,0),"Quarter",IF(IFERROR(SEARCH("Eighth",I2)>0,0),"Eighth",""))))),{"Spread","Full","Half","Quarter","Eighth"},0),2,1,0.5,0.25,0.125)

but Tony's solution is much clever and shorter

[sandy666]

Is this all definitions of basic names?
Spread
Full page
Half page
Quarter page
Eighth

and no more?

edit:
I suggest to use:
Spread
Full
Half
Quarter
Eighth
because word "page" has no matter

[mjattkatt]

No more those are the basic names, i will later on translate them to the correct language, used english in the example file for clarity

[Phuocam]

Or try this ...

=VLOOKUP("*"&I2&"*",Lookups!$A$2:$B$6,2,0)