VLOOKUP & INDEX/MATCH trouble

[jeffj]

I've read EVERYTHING there is in the XL Help, the MS Knowledge Base and the
Discussion Group but can't figure out why this isn't working...

I have an unsorted first column on worksheet 1 (BURKE) with empty cells as
well (it didn't make a difference without the empty cells, either). I am
trying to return the 3rd column to a cell on another worksheet by looking up
or matching the value in the 1st column. Here are the two versions I'm
trying:

=VLOOKUP(A3,BURKE!A:A,BURKE!C:C,FALSE)
A3 is the reference cell i'm trying to match, which has

100
120
130
140
etc.


I get #N/A or #VALUE! or #REF! when I paste it down the cells next to my
list, no rhyme or reason.

=INDEX("BURKE!",MATCH(A3,BURKE!A:A,0),BURKE!C:C)
(this one came right out of the Microsoft Knowledge Base)
gives me #VALUE! in all cells

I tried changing formatting to all number, all text, all general, but no
change.

Any help is appreciated.

Jeff

 

[Frank Kabel]

Hi
use:
=VLOOKUP(A3,BURKE!A:C,3,FALSE)

or
=INDEX(BURKE!C:C,MATCH(A3,BURKE!A:A,0))

 

[jeffj]

Thanks, the INDEX one works perfect. For future use, I can see that I had
them in the wrong order, but the Help and Knowledge base articles seemed to
lead me astray.

Sooo, for the VLOOKUP... A3 is my reference I wnat to match, BURKE!A:C is
where I want it to look (which must include all of the cells the formula
needs for reference and return?), 3 is what? and FALSE is the exact match
setting (TRUE would allow a closest match, right?)


The INDEX...BURKE!C:C tells where the return is coming from, the MATCH
refers to A3, then finds a match in BURKE!A:A and the 0=FALSE for an exact
match.

Am I close?

 

[Frank Kabel]

Hi
the '3' is the column index. that is it defined the column of the range
A:C which should be returned (in your case column 'C').

You may have a look at:
http://www.mvps.org/dmcritchie/excel/vlookup.htm