Hi
I want to know based on what logic vlookup() function will return a value if the last argument was TRUE not False. I tried to use True but could not figure the logic of vlookup() returned value.
VLOOKUP(lookup-value,lookup-table,result-column,match-type)
lookup-value, L: the value to look for.
lookup-table, T: the table in which to look for L.
result-column, R: a column of T from which to return a result value.
match-type, M: a flag that indicates how to look for L.
T may consist of 1 or more columns. B2:D5 for example counts 3 columns, which runs from B to D.
R runs from 1 to the number of columns T has. For B2:D5, we can have R =1, R = 2 or R = 3. [ But we can also have {2,3} here. ]
M can be either 0 or 1 (0 == FALSE; 1 == TRUE).
-----------------
The first column of T is the match range.
01.
=VLOOKUP(L,T,...)
means match L against the first column of T.
02.
=VLOOKUP(L,T,R,...)
means match L against the first column of T and return the value (result) from the Rth column of T which positionwise correlates with the match.
That is: If L = liza and this matches a value say in the third row of the first column of T, the result is a value from the third row of the Rth column.
03.
=VLOOKUP(L,T,R,0)
means match L against the first column of T where L equals a value in in the first column of T. That is, L = VLOOKUP(L,T,1,0).
Because L is here text, VLOOKUP is satisfied if
UPPER(VLOOKUP(L,T,1,0)) = UPPER(L)
04.
=VLOOKUP(L,T,R,1)
means match L against the first column of T where L is less than or equals a value in in the first column of T. That is, VLOOKUP(L,T,1,1) <= L.
T is assumed to be sorted on its match-range (on its first column) in ascending order.
Because L is here text, VLOOKUP is satisfied if
UPPER(VLOOKUP(L,T,1,1)) <= UPPER(L)
Thus, if L = lisa and lisa exists in the match-range then success because:
UPPER(VLOOKUP(L,T,1,1) = UPPER(L)
If L = lisa and lisa does not exist in the match range, VLOOKUP tries to find a value closer to L. If a closer value like lesa exists in the match range, then success because:
UPPER(VLOOKUP(L,T,1,1) < UPPER(L)
To recap:
When T is unsorted in ascending order on its match range, match-type = 0.
Otherwise, when T is sorted in ascending order on its match range, match-type = 1.
<strike></strike>
