Trying to make a conditional selection in Numbers

Question

Level 1

11 points

Trying to make a conditional selection in Numbers

I am working with multiple currencies, each with a different exchange rate. Ideally I'd like to create a small table with the different currencies and their respective exchange rates. In my main table with different transactions with different currencies, I'd like to automate the conversion to $.

For example, table 1 (with two column)

Euro 1.05

GBP 1.50

SFR 0.99

Then table 2 (3 columns)

Transaction 1 Euro 10000

Transaction 2 SFR 10000

Ideally, I'd want to create a fourth column in table 2 that would, for each row, look at the currency cell and then retrieve the relevant exchange rate for that currency in table 1.

So in the first row of table 2 above, the solution would go to table 1, find the Euro row, and retrieve the corresponding number i.e. 1.05

Is this possible? I am currently doing this all by hand!

Mac OS X (10.7.2)

Posted on Nov 29, 2015 1:03 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,336 points

Nov 29, 2015 3:52 PM in response to guy toronto

You can do something like this:

The formula in D2, copied down the column, is:

=VLOOKUP(B2,Rates::$A:$B,2,FALSE)

SG

Reply

Answer 2

guy toronto Author

Level 1

11 points

Nov 29, 2015 4:17 PM in response to SGIII

Thanks SGIII, works perfectly!

I'm new to VLOOKUP. Understand the basic principle, but can you explain why in the formula you took $A:$B for the column range? Why not just the Currency column in your Rates table? Forgive me for being a slow learner, and thanks so much for the help!

Reply

Answer 3

SGIII

Level 8

35,336 points

Nov 29, 2015 6:51 PM in response to guy toronto

Spreadsheets are complicate... even when one is used to them.

$A:$B here is known as the columns-range. The function looks for a match in the leftmost column of the range (here the leftmost column is column A) and the 2 in the formula tells it to return the value from column 2 (in other words column B here) of that columns-range.

You can read more on VLOOKUP here. I got to that page via Help > Formulas & Functions Help in the menu.

If you end up looking up things a lot then you can at some point learn about the INDEX MATCH type lookup, which is much more flexible than VLOOKUP. But first things first!

SG

Reply

Answer 4

Barry

Level 9

59,867 points

Nov 29, 2015 4:32 PM in response to guy toronto

HI Guy,

Here's the article on VLOOKUP from the function browser in Numbers '09. The function works the same way in Numbers 3. The first line, and columns-range answers your question.

VLOOKUP

The VLOOKUP function returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for: The value to find. search-value can contain any value type.
columns-range: A range of cells. range is a reference to a single range of cells, which may contain values of any type.
return-column: A number that specifies the relative column number of the cell from which to return the value. return-column is a number value. The leftmost column in the range is column 1.
close-match: An optional value that determines whether an exact match is required.
- close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than the search value. Wildcards can’t be used in search-for.
- exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can be used in search-for.

Usage Notes

VLOOKUP compares a search value to the values in the leftmost column of a specified range. Unless an exact match is required, the row containing the largest left-column value that is less than the search value is selected. Then, the value from the specified column in that row is returned by the function. If an exact match is required and none of the leftmost-column values match the search value, the function returns an error.

Reply

Answer 5

SGIII

Level 8

35,336 points

Nov 29, 2015 6:51 PM in response to guy toronto

The link provided in my post above is to an expanded version of the old Numbers 2 explanation, with more examples and pointers on how to get information on wildcards. And you can get that explanation in any of 35 languages (counting English as 2 and Chinese as 2). It's a great resource. When one's Mac is not online the Function Browser (in the right panel, which appears after you start entering a formula in the formula editor) can also be useful.

SG

Reply