Another Excel Code Query

The_Hurricane

Hi All,

What does the following code mean?

It's an old code that seems to be doing what I need it to but I want to try and understand the logic behind it.

=IFERROR(VLOOKUP(A5,'Football Data'!$A$5:$E$42,2,0),0)

Thanks

johnmc

VLOOKUP(A5,'Football Data'!$A$5:$E$42,2,0)
Is looking up a value from A5 on the "Football Data" tab.

It looks down Column A until it finds the first reference to the search and then brings in the value that's in Column B.

The "0" is the same as "False", so it will only match an exact value.


IFERROR(#####,0)
This looks at the value returned. If there is no value that matches it will show "0" rather than #N/A.

The_Hurricane

Perfect, thanks for that?

One final point, why does the code need two references to "A5"? There is the A5 before "Football Data" and there is $A$5.

Thanks again.

bossymoo

A5 is the value on the current sheet that its trying to match against on the football data tab.

gb12345

As johnmc says, but just to add that the $A$5:$E$42 in the VLOOKUP means that it will only find a match in rows 5 to 42, so if you ever add extra rows to the Football Data tab you would need to take account of that (either by increasing the 42 or by changing it to $A:$E)

The_Hurricane

Thanks everyone - excellent.

JimmyTheWig

The_Hurricane wrote: »

Perfect, thanks for that?

One final point, why does the code need two references to "A5"? There is the A5 before "Football Data" and there is $A$5.

The first A5 means the value of A5 in the current spreadsheet. I'm going to stick my neck out and guess this says "Arsenal".
The second A5 ($A$5:$E$42) is the start of the table in the other tab..

The cell below this will have A6 as the first argument, but the range will still start from $A$5.

bod1467

http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm

http://office.microsoft.com/en-gb/excel-help/overview-of-formulas-HP010342750.aspx

The_Hurricane

JimmyTheWig wrote: »

The first A5 means the value of A5 in the current spreadsheet. I'm going to stick my neck out and guess this says "Arsenal".
The second A5 ($A$5:$E$42) is the start of the table in the other tab..

The cell below this will have A6 as the first argument, but the range will still start from $A$5.

Arsenal - yes

JimmyTheWig

The_Hurricane wrote: »

Arsenal - yes

And is the table in the "Football Data" tab in alphabetical order, or is that in league position order?

jackomdj

Vlookups are great, I use them all the time. But remember they only pick up the first time the same things happen.

So if you are looking up Arsenal but it is for goals and it is on the football data tab twice it will only give you what is in column b next to the first time it happens.

Eg

A 5
B 2
C 7
A 3

If you are looking up a it would bring back 5 not 8.