Vlookup and Match

D

Dave O

VLOOKUP allows you to find an entry in a list and return a value that
is a given number of columns away from that entry. This can be a text
string or numeric value.

MATCH returns an integer number of the row within a range that matches
the entry in question.

You can next a MATCH function within an INDEX function and perform an
operation very similar to a VLOOKUP, because the INDEX function works
on a grid-type array of data.
 
N

Newbie

Thanks Dave. That is what I thought. I use Index, match and offset to
return values outside of the specified array, where vlookup seems limited eg:

=OFFSET(INDEX(samplearray,MATCH(C10,col,0),MATCH(C11,row,0)),-2,-3)

but thought I was missing something!
 
B

Bob Phillips

VLOOKUP looks in an mxn array, and returns the value in the row that the
lookup value is in, offset by the column offset number.

MATCH looks in either an mx1 or 1xn array, and just returns the index of the
matched item within the lookup range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
D

Dark_Templar

An other important difference between vlookup and index&match is tha
when using vlookup the look-up value needs to be in the leftmos
column.

Index&match doe not require that.

ie vlookup:

Col A Col B Col C
1 A HI
2 B Bye
3 C Yes
4 D No

If your lookup value was in Col B (lets say "B"), you could not look u
values form Col A ("2")

You could do it with index & match though
 
Top