Vlookup

  • Thread starter Thread starter April
  • Start date Start date
A

April

I have these data:

EmpID table (contains only empid):
EmpID
1415 (cell a1)
1500 (cell a2 etc..)
1587
1608
1800
1815
1925

Dept Table (contains deptid and the range of empid - and has
the range name "DEPT"):

From Empid To Empid Deptid
1400 1499 100
1500 1599 200
1600 1799 300
1800 1899 400
1900 9999 999

The result I'd like to see is:
EmpID Deptid
1415 100
1500 200
1587 200
1608 300
1800 400
1815 400
1925 999

I only know how to do simple vlookup
(=vlookup(a1,NAME,xxx,false) but don't know how to do a
range like this.

Your help is very much appreciated.

April
 
Assuming EmpID starts in A2 down

Try in B2: =VLOOKUP(A2,DEPT,3,TRUE)
Copy down

We can make use of DEPT's 1st column as the lookup col
as it is arranged in ascending order, with 4th arg set to TRUE
 
I believe that you have to sort your lookup table ascending on the first
column for this method to work. If you add, for example, department 500
with EmpID range of 1300 to 1399, put the 1300 entry above the 1400
entry. You may want to add a row at the top of your table with a value
of 0 for the employee ID and "Invalid Employee ID" in the Dept column as
an error trap.

BTW, the 'To EmpID' column is not used in your VLOOKUP; the limit for a
given record is based on the next value in the first column. Of course,
it doesn't hurt to leave it there, but remember that changing values in
that column will not affect your lookup.


Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
I believe that you have to sort your lookup table ascending on the first
column for this method to work. If you add, for example, department 500
with EmpID range of 1300 to 1399, put the 1300 entry above the 1400
entry. You may want to add a row at the top of your table with a value
of 0 for the employee ID and "Invalid Employee ID" in the Dept column as
an error trap.

BTW, the 'To EmpID' column is not used in your VLOOKUP; the limit for
selecting a given record is based on the next value in the first column.
Of course, it doesn't hurt to leave the column there, but remember
that changing values in that column will not affect your lookup.




Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
Try in B2: =VLOOKUP(A2,DEPT,3,TRUE)

Just to clarify that setting the 4th arg in the VLOOKUP above
to TRUE is equivalent to putting "1", or omitting it altogether
, viz. the above suggestion is the same as putting:

in B2: =VLOOKUP(A2,DEPT,3,1)
in B2: =VLOOKUP(A2,DEPT,3)

the last would be similar to Debra's suggestion <g>

(used TRUE to be "consistent" in structure
with the OP's indicated use of FALSE in her VLOOKUP)
 
Back
Top