KBV- vlookup question

[Guest]

In the vlookup formula, the range entered includes the columns that need to
be searched for matching or similar values and the range also includes the
column from which the corresponding value has to be returned. What if there
are 2 or more columns within that range that match the lookup value? How does
excel handle that?

 

[Ryan Christiansen]

According to Help for Excel 2003, VLOOKUP "searches for a value in the
first column of a table array." It only searches in that column for the
lookup_value argument.

-Ryan

 

[Bob Phillips]

=INDEX(rng_result,MATCH(1,(rng_lookup_1=value1)*(rng_lookup_2=val2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

If the value to be tested against is a string, enclose in quotes, or store
in a cell and refer to the cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I'm new to vlookup and am only familiar with the standard/basic vlookup
formula. Can you explain what the formula you wrote below means?

Thank you

 

[Dave Peterson]

Since you entered this as an array formula, excel will do essentially "for each
cell in this range, do something" comparisons.

rng_lookup_1=value1
will return an array of true/falses depending on whether each cell matches that
value1.

The same thing will occur for each cell in rng_lookup_2 compared to val2.

So you're left with an array of true/falses multiplied by another array of true
falses:

{true, false, true, false, true}*{false, true, true, true, true}
for example.

When excel multiplies booleans, it'll result in an array of 1's and 0's.

In my example:
{0,0,1,0,1}
(true * true = 1, false * anything = 0)

=match(1,{array of 0's and 1's},0)
will return the first position in that array that matches 1 which means it
returns the first position that matches value1 and val2.

=index(rng_result,somenumber)
will result in the somenumber-eth element of that rng_result range.

Simple as pie, huh?

But very powerful.