Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
I'm using MS Office Professional 2003.
I am frequently required to compile mailing lists from Excel spreadsheets containing extensive employee data. Situation: I have 2 worksheets in 1 workbook Sheet 1 has ID#, last name, first name, address1, address2, city, state, zip for all employees (~1000) Sheet 2 has ID#, last name, first name of employees receiving award letter (~200) How do I lookup and retrieve address information for award letter recipients from Sheet 1 and have that information correctly match and populate horizontally across cells in Sheet 2? EX: Sheet 1 Col A Col B Col C Col D Col E Col F Col G Col H 12345 Jones David 465 Main St. #1 Toledo OH 55555 25283 Baker Linda 7324 Elm St. Stamford CT 55555 Sheet 2 Col A Col B Col C Col D Col E Col F Col G Col H 12345 Jones David 25283 Baker Linda Thank you in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
Hi Cynthia, In sheet 2, col D, insert a lookup formula... =VLOOKUP(A2,[the range of data in Sheet1),4,FALSE So, for the house number, you would have =VLOOKUP(A2,Sheet1! A1:D1000,4,FALSE) This picks up the ID# in your awards sheet, compares it to the range you've specified, to find the ID number in the first column of the adresses sheet, then finds the entry in column 4 of that range. The "FALSE" at the end of the formula ensures that if no exact match is found, an #N/A error results. Hope that helps Phil |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
Phil,
Thank you - this works great! One question - is there a way to amend the command so that I can copy it across columns and have the Sheet 1 secondary column reference and location number change automatically (i.e.: from H39:L1000, 5, false to H39:L1000, X, false)? As it is, I have to do that manually each time I copy over to get address2, city, state, zip. Thank you, Cynthia -- Cynthia "Phil" wrote: Hi Cynthia, In sheet 2, col D, insert a lookup formula... =VLOOKUP(A2,[the range of data in Sheet1),4,FALSE So, for the house number, you would have =VLOOKUP(A2,Sheet1! A1:D1000,4,FALSE) This picks up the ID# in your awards sheet, compares it to the range you've specified, to find the ID number in the first column of the adresses sheet, then finds the entry in column 4 of that range. The "FALSE" at the end of the formula ensures that if no exact match is found, an #N/A error results. Hope that helps Phil |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
Hi Cynthtia,
I don't think there's a way to automatically up date the column number as you copy across the sheet - it's just a number as far as Excel is concerned and thus doesn't increment. However you need to make the range refrences absolute befor you copy, otherwise they will increment. Change your formula so they read $H $39:$L$1000, and also for the first cell reference in the formula. Actually, there are a couple of tricks that will at least reduce your editing.... 1 - In the first formula cell, highlight the range reference cells in the formula bar, then press F4 (This will add the dollar signs). The formula should look something like =VLOOKUP($A$1,$H$1:$L$1000,5,FALSE), with your own cell references in, of course! Press Enter. 2 - Use the Autofill handle to copy the formula to the rest of the row, then edit the colum numbers manually (a pain I know, but it's the only manual editing you'll need to do!) 3 - When finished, highlight the entire row containing the formulas and use the Autofill handle on the last highlighted cell and copy down as far as you need. This will copy the lot. 4. Use Search/replace to edit the $ signs out of the first cell reference in the formulae (the $A$1) to make it $A1. And that should do it all a bit quicker! Hope that helps, Phil |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
Woops - DON'T do the search/replace thing - Just put a $ in front of
the A in the first formula before you copy horizontally. The column letter won't change because of the $, and neither wil the row no's 'cos your filling horizontally. However you do need them to change when you copy the lot vertically. Hope that makes some sense! Phil |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text and Numeric lookup
Phil,
Thank you - all good info, especially the last post. This will save me hours of time and cross-checking. I appreciate your help!! Cynthia -- Cynthia "Phil" wrote: Woops - DON'T do the search/replace thing - Just put a $ in front of the A in the first formula before you copy horizontally. The column letter won't change because of the $, and neither wil the row no's 'cos your filling horizontally. However you do need them to change when you copy the lot vertically. Hope that makes some sense! Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text to numeric | Excel Worksheet Functions | |||
Lookup the last alpha numeric value | Excel Discussion (Misc queries) | |||
Lookup the last alpha numeric value in a column | Excel Discussion (Misc queries) | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
Text to Numeric | Excel Worksheet Functions |