I have a master worksheet (Worksheet1) that has two columns titled ID and Route. Each Route has a corresponding ID assigned to it. There are about 1600 rows (records) in this worksheet. I have another worksheet (Worksheet2) that also has an ID column (which is empty) and a Route column (which is populated). I need to fill in the ID numbers in Worksheet2 that correspond to the matching Routes in Worksheet1 without having to do it manually. Can anyone give me some guidance on this. Thanks in advance for any help.
Replies (3)
Please login or register to join the discussion.
You can do this......
Hi
If you have the column headers being in cells A1 & B1 on both WorkSheets and you put this formula in cell A2 in WorkSheet2, you can do:
=OFFSET(WorkSheet1!$A$1,MATCH(B2,WorkSheet1!$B$2:$B$1600,0),0)
This is basically a VLOOKUP looking in a column to the left of the table_array.
Any problems, let me know on: [email protected].
Regards
Elliott
Care with offset function
I use the offset function often and find it really useful.
However, it does make the spreadsheet harder to audit - the cells referenced don't appear in the cells referenced by other formulae in the audit tools.
Cheers, Kerry.