My formula works fine with no errors, but no value appears. What is the issue?
I am using another sheet for the lookup_table. The other sheets basically has the currencies and the rates of each currency. What I want to do in the sheet where the function is; basically once a row is added and the currency has been selected, I want it to generate the value of the currency from the other sheet. Therefore, once this formula works I want it to multiply it by the value of the PI. (Table below).
=IF(ISERROR(VLOOKUP([Currency (PI)]1, {currency rates Range 3}, {currency rates Range 4}, false)) = true, "", VLOOKUP([Currency (PI)]1, {currency rates Range 3}, {currency rates Range 4}, false))
This is the other sheet that I'm referencing from to get the rates.
As you can see from the first table.. it is linking fine without any errors but no value appears.
Thank you,
Fatema
Best Answer
-
There is an error with your VLOOKUP syntax, but the ISERROR function is replacing it with a blank cell. Where you are establishing a second range in the VLOOKUP, you need to replace that with an actual number. Your first range should cover all columns needed with the lookup value being in the leftmost column (column #1 of the range) and extend across your sheet to include the column you want to pull from.
In the examples you provided, the first range should be both the Currency and the Rates columns from your other sheet and then where you have the second range should be the number 2.
Give this a try...
=IF(ISERROR(VLOOKUP([Currency (PI)]1, {currency rates both columns range}, 2, false)) = true, "", VLOOKUP([Currency (PI)]1, {currency rates Range 3}, {currency rates both columns range}, 2, false))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
There is an error with your VLOOKUP syntax, but the ISERROR function is replacing it with a blank cell. Where you are establishing a second range in the VLOOKUP, you need to replace that with an actual number. Your first range should cover all columns needed with the lookup value being in the leftmost column (column #1 of the range) and extend across your sheet to include the column you want to pull from.
In the examples you provided, the first range should be both the Currency and the Rates columns from your other sheet and then where you have the second range should be the number 2.
Give this a try...
=IF(ISERROR(VLOOKUP([Currency (PI)]1, {currency rates both columns range}, 2, false)) = true, "", VLOOKUP([Currency (PI)]1, {currency rates Range 3}, {currency rates both columns range}, 2, false))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks a lot Paul. It worked!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!