Let's have a look at looking up data from tables. In this first example we want to look at using the Vlookup. I want to look up 25,000, and I can see that there are different tax rates for different income levels. I can also see that if I've got a income of less than 30,000, I'll be taxed at 30%. So between 10,000, 30,000 of income, I'll be taxed at 30%. So how do I get Vlookup to find that 30%? Well, if we type in our Vlookup, it then asks what value do you want to look up? I want to look up 25,000. It then asks, where do you want to look it up? Well, I want to look it up in C5 to D8.

It then asks me, which column do you want to return? Well, I don't want the income column, I want the tax column. So that is column 2. What Vlookup will do, it will look in the first column on the left and it will look for the closest number that is lower than 25,000. So it then goes to the 10,000 and returns the second column, it returns 30%. So Vlookup, really useful. What if we were looking up some text? Well let's use the Vlookup again. This time it asks me, what do you want to look up, want to look up A134. Then says, where do you want to look it up? I want to look it up in C14 to D18. And then asks me, what column do you want? I want the second column, I want the price. But I can now say that I want true or false. I want either an approximate match which would be the word true or I want false. I want an exact match, so I will choose false. It finds exactly the A134 and returns the price 3.5, great use that.

What about if we were to use Vlookup and match together? Well, let's start off with our Vlookup.

I want to find product ID A134.

It then asks, where do you want to go for looking for that? I want to go looking for that in C24 to F28. It then asks me, which column do you want to use? Well, I want to use the match function to find the price column. So I look up the word price. Where do I want to look for it, in C23 to F23, and I want an exact match 'cause I'm looking at text.

So close that. Lastly, we're back to the Vlookup, and again I want false, want an exact match. Press enter and it has found that 3.5. So what's it done? It firstly looks for the A134. It found that, it then asks how many columns should I go to the right? Price was in the second column, so I used the match function to find column 2, and then that returns to column 2, and the A134s intersection of 3.5. Let's try and do something similar, but this time using index and the match function. So index first of all asks you where's your array? Where do you want to go looking? Well, I want to look within C34 all the way down to F39.

That's my total table, it then asks me which row do you want to go looking in? Well, I want to look for A134. I could use the match function to find that. So my row is going to be match A134.

Where do I look, in C34 to C39. Match type, going to be 0 for exact.

It then asks me which column number, I'm back to the index calculation again, which column number do you want? I'm gonna choose another match function. So I look up the value, price. Where do I look for that? I look for that in C34 to F34.

Again, I want that to be an exact match. Close the brackets, press enter, and that has found the 35 again. So I use the match function to find the A134 and I use the match function to find the price and the intersection of the two was the 3.5. Let's do this one more time but this time using the offset function and match. So I type in my offset function and I need a reference point, a starting point. So I'm gonna start in C45. It then asks me how many rows. Well, whenever it's asked me for rows or columns I can use that match function. So I'm gonna look up the A134. Where am I going to look for it, in C46 to C50, and my match type, I want a 0, I want an exact match. Back to my offset function again, I'm now asked how many columns do you want to go to the right? Well, I can use the match, again. I want to match the word price. Where do I want to look for that? I want to look in D45 to F45. So slightly different from what we saw at the index. We don't include C45 in our match functions at all.

I want an exact match again, close the final brackets and press enter and it's found that 3.5 again. So what do we notice this time? We notice that our three areas that we've highlighted don't overlap at all. Whereas when we looked at the index function, they did overlap. My column, my row, and my table array all overlapped.