Vlookup Question with formatted cells

[dollyann]

Please can someone help me with a problem?

I have a spreadsheet with some costs and they are both in Pounds and Euros, like below

London £3.00
France €2.10
Spain €50.00
London £21.00

I have a dashboard and when I select France i have set-up a vlookup to bring the value back (2.10), is there anyway that i can bring back value with the formatted cell with the Euro symbol €?

Would be very grateful if someone could help me

Many thanks

 

[sijpie]

I'm not sure about excel 2007 & later, but earlier versions you can only do it using a macro that checks either the country or the formatting and then sets the formatting accordingly.

I don't know if you want to work with macros, it adds additional issues if the sheet is to be used by others

 

[Akashwani]

Hi dollyann,

Perhaps you could modify this.....

=IF(D2<>"London",CHAR(128)&VLOOKUP(D2,A2:B4,2,0),CHAR(163)&VLOOKUP(D2,A2:B4,2,0))

Excel Workbook
	A	B	C	D	E
1	Country	Amount	*	Country	Amount
2	France	200.00	*	London	150
3	Spain	250.00	*	*	*
4	London	150.00	*	*	*
Sheet7

CHAR(128) is the euro symbol and CHAR(163) is the £

The only problem with the above is that it will return the data as text and not as a number, but I'm sure there may be a way to convert it to a number.

Good look.

Ak

 

[scottylad2]

create a table with the country names and the charachter that is their curency, then combine 2 vlookups based on the same criteria.

Excel Workbook
	A	B	C	D	E
1	Country	Amount		Country	Amount
2	France	200.00		France	200
3	Spain	250.00
4	London	150.00
5	USA	$750.00
6
7	France	Euro
8	Spain	Euro
9	London	pound
10	USA	Dollar	$
Sheet1

 

[Akashwani]

Hi dollyann,

If you need the Vlookup result to be a true number so that you can calculate it etc, then perhaps some reformatting of your worksheet is a possible solution...

Excel Workbook
	A	B	C	D	E	F	G
1	Country	Amount	Symbol	*	Country	*	Amount
2	France	200.00		*	London		150.00
3	Spain	250.00		*	*	*	*
4	London	150.00		*	*	*	*
Sheet7

You can hide column C or use a white font.

Good luck.

Ak