| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up march and copy info back - Help! confused!!
I have a spreadsheet with 2 tabs.
First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
Excel Office 2003
"Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
OK, keep in mind that you run the risk of failure if:
The First names in each sheet are not identical - i.e., Walter in one and Wally in the other. Ditto for last names - and for both first and last this could also mean that one list has spaces after the names and the other doesn't. If you have more than one Wally Smith, or Fannie Jones. I'm sure there are other causes for failure, too, beyond these Anyway, create a new column A on your first sheet and in it enter this formula (remember that the First & Last names have been bumped over one column because of the new column A) =J2&" - "&K2 Using this you have a new column of names in the form "Wally-Smith" On the Users sheet use this ARRAY formula. Array formulas are entered by pressing Ctrl-Shift-Enter =VLOOKUP(b2&"-"&c2,Inventory!$a2:$b8279,2,0) Copy that formula down "Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
Try this array formula** :
=INDEX(Inventory!A$2:A$8729,MATCH(1,(Inventory!I$2 :I$8729=B2)*(Inventory!J$2:J$8729=C2),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Seantastic" wrote in message ... I have a spreadsheet with 2 tabs. First tab is named "inventory" and has a list of "computer names" in column "A" (from row 2 to 8729) the user "First Name" in column "I" and the users "Last Name" in column "J". There is no particular order in this list and the names are in some random order. My second tab is named "Users" and has a list of the user "First Name" in column "B" and the users "Last Name" in column "C". Column "A" is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the "Users" tab look it up (match) the Same First and Last Name in the "inventory" and copy the associate "computer name" from Column "A" back into the blank column "A" of my "Users" tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab "inventory" in would not continue lower 3) Did not go through all the list in tab "inventory" 4) Did not work - I do not really know what I am doing!! vvvv |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
First of all, as your data is not in order you will need to set the
4th (optional) parameter in VLOOKUP to 0 or FALSE, so that you can look for exact matches. Secondly, you need both first name and last name to be matched. Thirdly, VLOOKUP will only return data which is to the right of the lookup column, so you can't use VLOOKUP with what you have. However, if you insert a new column A in the Inventory sheet and put this formula in A2: =K2&J2 and then copy down (you can hide this new column A so that the sheet looks the same as before), then you will be able to do this in A2 of your Users sheet: =VLOOKUP(C2&B2;Inventory!A$2:B$8729;2;0) Copy this down as required. Hope this helps. Pete On Oct 29, 8:21*pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
Thanks but - My blank "A" column already exists in my "Users" tab where I
want the computer names to be copied to from the other list (inventory) I copied the formular into the column "A" of my "User" tab and it did not work (contains errors) inventory Tab A I J computer Name First Last Users Tab A B C -blank- First Last I want to find the same First & last Names that appear in "Users" Tab in the "inventory" Tab then copy the associate info from column A back into the blank column A of the "Users" Tab. "Duke Carey" wrote: OK, keep in mind that you run the risk of failure if: The First names in each sheet are not identical - i.e., Walter in one and Wally in the other. Ditto for last names - and for both first and last this could also mean that one list has spaces after the names and the other doesn't. If you have more than one Wally Smith, or Fannie Jones. I'm sure there are other causes for failure, too, beyond these Anyway, create a new column A on your first sheet and in it enter this formula (remember that the First & Last names have been bumped over one column because of the new column A) =J2&" - "&K2 Using this you have a new column of names in the form "Wally-Smith" On the Users sheet use this ARRAY formula. Array formulas are entered by pressing Ctrl-Shift-Enter =VLOOKUP(b2&"-"&c2,Inventory!$a2:$b8279,2,0) Copy that formula down "Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
It gave me an error (formular contains errors)
"T. Valko" wrote: Try this array formula** : =INDEX(Inventory!A$2:A$8729,MATCH(1,(Inventory!I$2 :I$8729=B2)*(Inventory!J$2:J$8729=C2),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Seantastic" wrote in message ... I have a spreadsheet with 2 tabs. First tab is named "inventory" and has a list of "computer names" in column "A" (from row 2 to 8729) the user "First Name" in column "I" and the users "Last Name" in column "J". There is no particular order in this list and the names are in some random order. My second tab is named "Users" and has a list of the user "First Name" in column "B" and the users "Last Name" in column "C". Column "A" is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the "Users" tab look it up (match) the Same First and Last Name in the "inventory" and copy the associate "computer name" from Column "A" back into the blank column "A" of my "Users" tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab "inventory" in would not continue lower 3) Did not go through all the list in tab "inventory" 4) Did not work - I do not really know what I am doing!! vvvv |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
Super! This worked well! Thanks!!
"Pete_UK" wrote: First of all, as your data is not in order you will need to set the 4th (optional) parameter in VLOOKUP to 0 or FALSE, so that you can look for exact matches. Secondly, you need both first name and last name to be matched. Thirdly, VLOOKUP will only return data which is to the right of the lookup column, so you can't use VLOOKUP with what you have. However, if you insert a new column A in the Inventory sheet and put this formula in A2: =K2&J2 and then copy down (you can hide this new column A so that the sheet looks the same as before), then you will be able to do this in A2 of your Users sheet: =VLOOKUP(C2&B2;Inventory!A$2:B$8729;2;0) Copy this down as required. Hope this helps. Pete On Oct 29, 8:21 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
It gave me an error (formular contains errors)
Hmmm... There's nothing wrong with the formula. Did you enter it as an array? ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Seantastic" wrote in message ... It gave me an error (formular contains errors) "T. Valko" wrote: Try this array formula** : =INDEX(Inventory!A$2:A$8729,MATCH(1,(Inventory!I$2 :I$8729=B2)*(Inventory!J$2:J$8729=C2),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Seantastic" wrote in message ... I have a spreadsheet with 2 tabs. First tab is named "inventory" and has a list of "computer names" in column "A" (from row 2 to 8729) the user "First Name" in column "I" and the users "Last Name" in column "J". There is no particular order in this list and the names are in some random order. My second tab is named "Users" and has a list of the user "First Name" in column "B" and the users "Last Name" in column "C". Column "A" is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the "Users" tab look it up (match) the Same First and Last Name in the "inventory" and copy the associate "computer name" from Column "A" back into the blank column "A" of my "Users" tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab "inventory" in would not continue lower 3) Did not go through all the list in tab "inventory" 4) Did not work - I do not really know what I am doing!! vvvv |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look Up march and copy info back - Help! confused!!
You're welcome - thanks for feeding back.
Pete On Oct 29, 9:09*pm, Seantastic wrote: Super! This worked well! Thanks!! "Pete_UK" wrote: First of all, as your data is not in order you will need to set the 4th (optional) parameter in VLOOKUP to 0 or FALSE, so that you can look for exact matches. Secondly, you need both first name and last name to be matched. Thirdly, VLOOKUP will only return data which is to the right of the lookup column, so you can't use VLOOKUP with what you have. However, if you insert a new column A in the Inventory sheet and put this formula in A2: =K2&J2 and then copy down (you can hide this new column A so that the sheet looks the same as before), then you will be able to do this in A2 of your Users sheet: =VLOOKUP(C2&B2;Inventory!A$2:B$8729;2;0) Copy this down as required. Hope this helps. Pete On Oct 29, 8:21 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory and has a list of computer names in column A (from row 2 to 8729) the user First Name in column I and the users Last Name in column J. There is no particular order in this list and the names are in some random order. My second tab is named Users and has a list of the user First Name in column B and the users Last Name in column C. Column A is blank. There is no particular order in this list and the names are in random order. I want excel to take the First Name and Last Name that are in the Users tab look it up (match) the Same First and Last Name in the inventory and copy the associate computer name from Column A back into the blank column A of my Users tab. I tried this functions =VLOOKUP(C2;Inventory!J2:K8729;2) but the problem I got was 1) It only looked at Last Names (and I have a few Smiths) 2) When it encountered blank spaces in tab inventory in would not continue lower 3) Did not go through all the list in tab inventory 4) Did not work I do not really know what I am doing!! vvvv- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2008 Australian Excel Conference - March 12 - March 14, Sydney, Australia | Excel Discussion (Misc queries) | |||
| 2008 Australian Excel Conference - March 12 - March 14, Sydney, Australia | Excel Worksheet Functions | |||
| 2008 Australian Excel Conference - March 12 - March 14, Sydney, Australia | Excel Worksheet Functions | |||
| 2008 Australian Excel Conference - March 12 - March 14, Sydney, Australia | Excel Discussion (Misc queries) | |||
| 2008 Australian Excel Conference - March 12 - March 14, Sydney, Australia | Excel Worksheet Functions | |||
Linear Mode
