vlookup not working

[Juan]

I am trying to do a VLOOKUP so that the FAR column from TABLE 2 gets filled
into the FAR column of TABLE 1. I keep getting the N/A error. I tried sorting
and changing text to numbers and vice versa.

I had them on separate workbooks but now i placed both tables on the same
sheet hoping for a difference. It is a very long list of 6333 names on the
first table and 7227 names on the second table. Below is the formula i used
in cell F2 with a sample of 3.

=VLOOKUP(B2,H1:M7227,6,FALSE)

TABLE 1
A B C D E F
School Perm ID. Last Name First Name Grade FAR
327 1740304 Mouse Mauricio 12 #N/A
302 10009557 Cat Jessica 12 #N/A
302 10011618 Smith Paloma 12 #N/A

TABLE 2
H I J K L M
School Perm ID. Last Name First Name Grade FAR
327 1740304 Mouse Mauricio 12 N
302 10009557 Cat Jessica 12 N
302 10011597 Smith Robert 12 Y

 

[bapeltzer]

The big issue is that the table (H1:M7227 in your case) needs to have the key
value you're looking for in the first column. Since you're looking up the
PermID, change the table_range to start in column I. Once you do that, you
also need to change the column_no to 5. Finally, you probably want to use
absolute references for the table. The result:
=VLOOKUP(B2,$I$1:$M$7227,5,FALSE).

 

[Juan]

Thank you very much! That worked. I didn't think it would make a difference
where the key started. Also, I guess I need to learn more about absolute
references becuase I thought that by adding those, it would return the same
field every time.

Thanks again.

 

[Gord Dibben]

For the Table Range, you want it to be the same all the time.

For the Lookup Value.........B2..........you want it to increment as you
copy down.


Gord Dibben MS Excel MVP