Function explaination need

G

Guest

Previous staff have form the function below and I don't understand what it
means. He was no more longer work here. I need someone to explain what it say.

Here is the function look like
=IF(ISERR(VLOOK($A7, returnall, 32,0)*100) ,“ “,VLOOKUP($A7, returnall,
32,0)*100)

Can anyone also explain why the 32 can be change to any number, what it
represent?

Thanks for anyone trying to help
Cheer,
hoachen
 
P

Peo Sjoblom

A more proper way would be

=IF(ISNA(VLOOK($A7, returnall, 32,0)),"",VLOOK($A7, returnall, 32,0)*100)

and you can change 32 to whatever fits within the named range "returnall"
You cannot change it so it tries to pull for a column that is beyond the
last column in "returnall"
 
P

Pete_UK

If you do Insert | Name | Define then you should see returnall listed
in the panel - if you select this then at the bottom of the panel it
will tell you what this name refers to. It is obviously a table and
might have references something like Sheet2!$A$1:$AM$200 - this shows
that the table occupies rows 1 to 200 in Sheet2 and columns A to AM
(i.e. 39 columns wide).

The lookup formula is trying to find an exact match between the value
in A7 and the values in the first column of the table returnall - if
there is an exact match, then the value returned from this formula is
that from the 32nd column of the table on the same row as the matched
value. Hence the number 32 can only vary to suit the number of columns
defined in the table (in my example, it could be from 1 to 39).

If there is no exact match, however, the lookup function would
normally return an error. Rather than display this error, the formula
will display what looks like an empty cell, although it is actually a
<space>. So basically the formula means "If there is going to be an
error in using this lookup formula, then return a <space> instead, but
otherwise return the value from the 32nd column of the table returnall
on the row where an exact match with A7 is found, and multiply this by
100".

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top