Explanation of attached formula requested.

[Bill]

A kind expert responded to a query I posted regarding =if
and Vlookup. It worked of course but I need to understand
why for the future.
I believe I have a general understanding of the formula
but would be grateful if someone could confirm why it is
necessary for 2 sets of "" in the formula and the reason
for the final zero

=if(trim(a6)="","",vlookup(A6,Scanner_table,2,0))

My understanding is that if I enter data (with any spaces
removed) into A6 the system looks in Array Scanner_table
column 2 for data matching that which was entered in A6
and places it in my spreadsheet in the cell where the
formula exists. Does the final Zero mean that if data
doesn't match, then enter 0 (which actually presents #NA
in that cell?

TIA
Bill

 

[Arvi Laanemets]

Hi

1. If A6 contains nothing or only spaces, the formula returns an empty
string, otherwise it looks up for value in Scanner-table;

2. VLOOKUP looks in Scanner-table for exact match of A6. When 4th parameter
is 0 or FALSE, the VLOOKUP searches for an exact match - when not found, the
formula returns an error The search range can be unordened. When 4th
parameter is 1 or TRUE, the nearest match is searched for. The search range
must be ordened - otherwise you can get the wrong result.

 

[JulieD]

Hi Bill

the IF function takes three parameters

the test, what to do if the test evaluates to true, what to do if the test
evaluates to false
so in your case if there is nothing in A6 (trim(a6)="") then display nothing
(or more accurately a zero length string) in the cell "" (the true part),
however if there is something in A6 then do the VLOOKUP function.

the reason we nest a VLOOKUP in an IF is that if a value is not found in the
VLOOKUP table a #NA is returned and this doesn't look terribly pretty and,
of course, you're not liable to have a cell in the first column of your
VLOOKUP table with nothing in it.

the VLOOKUP function takes four parameters - the first three of which are
mandatory and the fourth is optional
the value to look up, the table with the answer, the actual column number of
the answer and whether or not you want to perform an approximate match.

if you don't put a fourth parameter in the VLOOKUP statement, excel assumes
that you want to do an approximate rather than an exact match (ie TRUE is
assumed). So to "force" a exact match we use FALSE or 0 (which means false)
....

Oh, while i'm at it, the TRIM function removes spaces from around an entry
in a cell ... so in your case if there is the situation where someone has
pressed the space bar in the cell but not actually typed anything it will be
changed to a zero length string and evaluated as such (in the true)
statement, or if there is a space & text in the cell the space will be
removed so that the text can be matched against text in the vlookup table.

Hope this explains it

Cheers
JulieD

 

[Gary Thomson]

Bill,

An explanation of your formula is as follows:

The two sets of "" are needed as vlookup will give an
error if it is looking for a blank.

I.e. what the formula is saying is that:

(1) it will look up the value in cell A6 in the
Scanner_table array and return the value in the same row
but from the second column of this array,

UNLESS

(2) a6 is blank (this is what if(a6)="" searches for) or
if a6 only contains spaces (this is what the trim function
does to a6).

The Trim only applies to the IF statement, and does not
affect the VLOOKUP (thus you don't have to enter data with
the spaces removed).

VLOOKUP returns N/A if a match is not found. The zero at
the end of this is equivalent to putting false, which
means that unless VLOOKUP finds an exact match, it will
return N/A. Putting 1 (or TRUE) at the end of the vlookup
(instead of 0) means that vlookup will return the next
largest value that is less than the lookup value.

Please feel free to come back to me if you require further
clarification,

Happy to Help,

Gary Thomson