get the VLOOKUP function to return the "contents" of a cell?

S

sbellybutton

=VLOOKUP(B1:B730,Sheet2!A1:C216,2,1) returns #N/A. What am I doing wrong?
Row1 of my table is #s and row2 is letters. I need the 730 cells in rowB to
equal what is in my table! Help.
 
P

Pete_UK

I suggest you change it to this:

=VLOOKUP(A1,Sheet2!A$1:C$216,2,1)

entered in B1, then copy the formula down to row 730. I think you are
getting "rows" and "columns" mixed up in your description.

Hope this helps.

Pete
 
S

sbellybutton

I tried both formulas and recieved the same answer then notices the numbers
in col.A are not alligned the same as the numbers in col.A on the table
sheet! Feeling retarded HELP
 
G

Gord Dibben

=VLOOKUP(B1,Sheet2!$A$1:$C$216,2,1)

Copy down column B to B730

NOTE: B is a column, not a row


Gord Dibben MS Excel MVP
 
S

sbellybutton

I also need to know how to get the correct contents if there are letters and
numbers in all both col.s of sourse and table...
 
D

Dave Peterson

I put the table in Sheet2 in A1:C216.

I put something in B1 of sheet1 that matched that first column in the table in
Sheet2 (A1:A216).

Then I used this formula
=vlookup(b1,sheet2!a1:c216,2,false)
to return the value in column B of sheet2 when B1 matched the value in A1:A216
of sheet2.

I'm not sure what you did. You may want to explain it in more detail.
 
S

sbellybutton

Sheet1 (source)
b
22 #N/A
55 #N/A
b
26 #N/A
5
3
Sheet 2 (table)
b 0
161 d
162 e
163 E
164 g
165 h
166 i
167 j

I am trying to fill in blanks on sheet1 according to table..
 
D

Dave Peterson

If there's no match, then you'll see #n/a.

You can hide those errors by:

=if(iserror(vlookup(b1,sheet2!a1:c216,2,false)),"",
vlookup(b1,sheet2!a1:c216,2,false))

(It looks for the error and if found returns "" (looks blank). If no error, it
retrieves the matching value.)
Sheet1 (source)
b
22 #N/A
55 #N/A
b
26 #N/A
5
3
Sheet 2 (table)
b 0
161 d
162 e
163 E
164 g
165 h
166 i
167 j

I am trying to fill in blanks on sheet1 according to table..
Click to expand...
 
Top