How To Convert Words and Names To Values in Numbers With IF, SWITCH and LOOKUP

If you have names, product IDs or some other words and need to translate those to number values you can do it with the IF, IFS, SWITCH or LOOKUP functions in Numbers. While the first functions are good for small limited lists of values, only the LOOKUP function works well with a large list that changes often.

Video Transcript: Hi. This is Gary with MacMost.com. Let me show you how to use the IF, Switch and Lookup Functions in Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So often in Numbers you have something like a product ID or name and you want to convert that to a number for use in your spreadsheet. For instance here in this spreadsheet I've got product names here and there's a fixed price for each product. I could enter it in manually but it would be nice that every time I typed the word apple, the price automatically filled in. The same for orange and any other product. Then I could perform calculations like multiple it by the quantity to get the total without having to rely on remembering the exact price every time. Plus, if the price changes I would have to go in to change it in every single cell where that price appeared. 
So there are many ways to solve this problem in Numbers and it really depends on how many different names or products you have. If you only have two then you can use a simple IF function to do it. So here under Price I'm going to hit the equals key to start typing a formula. Then I'm going to enter the formula IF and then a parentheses. Then I'm going to click here to select this cell as the value. Then I'm going to use equals and then in quotes put apple. So this is going to be IF B2 = apple. It's evaluating this expression here and is getting a True or False. If it is True then the next parameter will be the values. So let's say it's 0.15 for 15 cents. Let's say if it is not an apple, if it is something else, it's 0.10 for ten cents. Then I close the parentheses and there is my final formula. IF B2 +=apple then it is 15 cents, otherwise it is 10 cents. Now when I click the checkbox there you could see it performs the function and gives me 15 cents. 
If I were to Copy that to the next cell down and paste it in it gives me 10 cents. Notice that the formula updated the cell reference from B2 to B3. I copied the formula from row 2 and pasted it in row 3 so it knew to increase the value of any cell references by one row. So now I could Copy and Paste that into all of these cells. An easy way to do that is to Copy it once and then double click on the letter above the column. When you double click it selects all the cells but not the Header or Footer rows. Then I can Command V to Paste. So now you can see it does the correct values for those and I get all of these false values for the empty rows. Which really shouldn't be a problem because you shouldn't actually have any empty rows. I'm going to Delete those and then shrink this table so that it just has rows that have values. If I want to add another one I can just hit Return in any cell there and you can see how it automatically carried that formula over. 
So now I can enter in the data here and you could see how it updates right there because the formula reacts to this value. Then I could enter a quantity and maybe I could put a formula in here that calculates the total. Price times Quantity. I'll do the same thing here. Copy, double click on the letter at the top of the column and Paste in and now I get these values. To make it look even better let's select all of these cells again here. Go to Cell and change the format to Currency. You could see how this one picks it up because now it's a currency format times quantity and it gives us a currency result. 
So what happens if you have more than two things? For instance what if the next row was a completely different product. Like peaches. Well, it is still going to be something other than apple. So it's going to apply that second value there, the ten cents. But that's not what we want. What we want is to actually have three separate values. Apple, orange, peach. So let's go and change the formula in there. I could use a nested IF statement to do this. So I've got the comparison there. B2=apple then it is 15 cents. Otherwise what. So instead of giving a value for every other situation I'll do another IF statement in here. This is called nesting functions. I'll nest an IF statement inside of an IF statement. I'm going to do the same comparison in here. This cell equals, but this time orange, and then the price is going to be 0.1. Otherwise, and I'll nest another IF statement this cell is equal to peach. Then it is going to be 0.12. Otherwise, and I don't have any other values here but I'll just use a zero as the default value. So the product isn't in this list of nested IF statements then it will be zero. 
So I have IF B2 is apple then it is 15 cents. Otherwise IF B2 is orange then it is ten cents. Otherwise IF B2 is peach than 12 cents. If I Copy and then Paste this throughout you could see it works for all of these. Let me give this sale here a quantity and then I get a total. Now the problem with this is you can see how long it gets for just three products. Imagine if you had ten or twenty or a hundred. So let's clear these out and try another approach. 
There's a variation on the IF function called IF S or IFS. This looks a little different. If we look in the functions Help here on the right for IFS we can see how this works. It's an expression and then if it is True the value. Then another expression and if that is True and so on. So we'll do IFS this is equal to apple then 15 cents. Then we repeat that. IFS this is equal to orange than ten cents. Then IFS this equal to peach than 12 cents. That's it! Now you could see here it's not that much shorter than having nested IFS but it is easier to read. You could clearly see the comparison and the result. Comparison result, Comparison result. If we Copy that and Paste it throughout it works. But you could see how this is still going to get pretty long if we add more and more things. 
Now there's a new function if you have the latest version of Numbers. Numbers 12.2. This new function is called Switch. Let's use that instead. So if I look up Switch here you could see it takes expression and then value and then IF match. So we can start off here with SWITCH. So now we only have to reference the cell once. Then we have what the value would be for the first match, apple. Then we do 0.15 and then second value and then 0.1 and then the third value 0.12. So this is a little easier to read. You can see it's a lot shorter and it only references this cell one time. So just the reference and then pairs of, IF it's this then that, IF it's this then that, IF it's this then that. If we want we can have a default value at the very end. Just by itself. So now I can Copy and Paste this and it works just the same. It's just a little bit shorter of formula. A little easier to see what it going on. Now if we add a new row here we can then add apple and you can see how it changes and add a quantity and then we get our total. 
So this definitely is a lot easier to use than having nested IF functions or using the IFS function. But it is till not great. If you've got dozens or hundreds of different things to match or if you want to make it easier to change things. After all if I were to change a value here, like change this to .11, I would have to Copy and Paste that formula throughout the entire column again. So any change is going to mean some work in the spreadsheet here. I can't just change the value in one spot and have it reflected everywhere else. To do that you could just use the Lookup function. The Lookup function uses a second table. So let's create that table. 
I'm going to click on the Table button here and I'm going to select the most basic kind of table like this. I only need two columns so I could shrink this down and let's move it right next to the Sales table here. This could actually be a table on another sheet if you like. You don't have to put it side-by-side in the same sheet. But let's do that so we can see them both. I'll call this the Lookup Table. You can call it whatever you want. Let's put Product here and Price here. Then let's list our products. We have apple, orange, and peach. The prices for those are 0.15, 0.1, and 0.12. Let's make those Currency and we can shrink this table up because we only need those three rows. Now using this table we use the Lookup Function. Now we can put the Lookup function right here. So let's go and look up the Lookup Function. So first you start off with the value to search for. Then where to look for that value. Then where to get the result from. So the value to search for would be this. Now where to look. Well, it's going to look in this column. So click on A at the top of that first column there. It's going to take the entire column into account. Then where to find the result. That's going to be B. So what is going to happen is it is going to look for the value here, in this case apple in column A. When it finds it it is going to take the corresponding cell in row B and use that as the value. So that's just it. Just that simple Lookup Function and it will do it. If I Copy and Paste throughout you could see it works. 
Better still if I want to change something, like I want to change the price of oranges to 11 cents, I change it right here and you could see how it updated it all throughout here. I didn't have to Copy and Paste a new version of the formula throughout that column. I just changed that value and now the Lookup Function will find the new value there. If I want to add a completely new product I can. Let's just go and add a new one here and called it that. Then let's enter the quantity here. Then all I need to do is add a row here and then go and type the value like that. Now you can see how it picks it up. So I can easily add new products without ever updating the formula at all. I can add dozens or hundreds of products here in this list and it will be able to look them up. 
So there are various ways to convert a name or ID or something to a value in Numbers. As you can see the most versatile way is to simply use the Lookup Function. It just takes an extra table and then the formula itself is relatively simple. I hope you found this useful. Thanks for watching.

Comments: 2 Responses to “How To Convert Words and Names To Values in Numbers With IF, SWITCH and LOOKUP”

Bill Ferrol

1 year ago

Hi Gary, great stuff. To speed up copying formulae rather than double-clicking the top of the column twice, then deleting rows you don't need and finally pasting the copied formula/formulae, why don't you show hovering the cursor over the cells containing the formulae, then moving the cursor slightly over the top/bottom/side of the cell to display a yellow dot, then drag the dot that appears in the direction of the cells you want to fill? The formulae are copied in one click and drag! Regards

Gary Rosenzweig

Bill: That method is only a but more useful if you don't want to fill every row. But why would you want some rows to not use the formula? That would suggest that your table isn't uniform: some rows have records of data in them and others do not, or are storing different types of records. Use another table if you have more than one type of record.

Comments Closed.

How To Convert Words and Names To Values in Numbers With IF, SWITCH and LOOKUP

Comments: 2 Responses to “How To Convert Words and Names To Values in Numbers With IF, SWITCH and LOOKUP”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information