-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
need formula for currency
- Thread starter zoharb
- Start date
6StringJazzer
Well-known Member
- Joined
- Jan 27, 2010
- Messages
- 3,286
- Office Version
- 365
- Platform
- Windows
- Mobile
You need a lookup table that lists country names and corresponding currency codes. Then use XLOOKUP or VLOOKUP depending on your version of Excel
Lookup table (this is just the beginning)
Results:
Lookup table (this is just the beginning)
| Packing Checklist.xlsm | |||||
|---|---|---|---|---|---|
| G | H | I | |||
| 1 | Country | Currency | Code | ||
| 2 | Afghanistan | Afghani | AFN | ||
| 3 | Åland Islands | Euro | EUR | ||
| 4 | Albania | Lek | ALL | ||
| 5 | Algeria | Algerian Dinar | DZD | ||
| 6 | American Samoa | US Dollar | USD | ||
| 7 | Andorra | Euro | EUR | ||
| 8 | Angola | Kwanza | AOA | ||
| 9 | Anguilla | East Caribbean Dollar | XCD | ||
| 10 | Antigua And Barbuda | East Caribbean Dollar | XCD | ||
| 11 | Argentina | Argentine Peso | ARS | ||
| 12 | Armenia | Armenian Dram | AMD | ||
| 13 | Aruba | Aruban Florin | AWG | ||
| 14 | Australia | Australian Dollar | AUD | ||
| 15 | Austria | Euro | EUR | ||
| 16 | Azerbaijan | Azerbaijanian Manat | AZN | ||
| 17 | Bahamas | Bahamian Dollar | BSD | ||
Currency Rates | |||||
Results:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L2 | L2 | =XLOOKUP(K2,$G:$G,$I:$I) |
| L3 | L3 | =VLOOKUP(K3,$G:$I,3,FALSE) |
Upvote
0
zoharb
New Member
- Joined
- Nov 24, 2011
- Messages
- 42
- Office Version
- 2021
- 2013
Respected Mark,
Thank you , the formula given by you worked
Can it be also made to work even if
"united states" is "unitedstates"
"new zealand" is "newzealand"
"united kingdom" is "unitedkingdom"(means not having spaces)
i tried using OR but it was not working.
PLEASE Please try from your end , if it works or some other way.
PLEASE
Zohar Batterwala
Upvote
0
MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,271
- Office Version
- 365
- Platform
- Windows
- Mobile
They all need adding separately as per the others in the formula BUT if you are having that many alternatives then you seriously need to have a separate Lookup table as @6StringJazzer has posted.
Having more than 5 lookup's in the formula makes it very difficult to manage down the line
Having more than 5 lookup's in the formula makes it very difficult to manage down the line
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | UNITED STATES | USD | ||||||
| 3 | CANADA | CAD | ||||||
| 4 | UNITED STATES | USD | ||||||
| 5 | UNITED KINGDOM | GDP | ||||||
| 6 | NEW ZEALAND | NZD | ||||||
| 7 | UNITEDSTATES | USD | ||||||
| 8 | UNITEDKINGDOM | GDP | ||||||
| 9 | NEWZEALAND | NZD | ||||||
| 10 | ||||||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F9 | F2 | =IFERROR(VLOOKUP(B2,{"CANADA","CAD";"NEW ZEALAND","NZD";"NEWZEALAND","NZD";"UNITED KINGDOM","GDP";"UNITEDKINGDOM","GDP";"UNITED STATES","USD";"UNITEDSTATES","USD"},2,0),"") |
Upvote
1
Solution
Similar threads
- Question
