Look Up march and copy info back - Help! confused!!

[Seantastic]

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

 

[Seantastic]

Excel Office 2003

 

[Duke Carey]

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

 

[T. Valko]

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)

 

[Pete_UK]

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

 

[Seantastic]

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.

 

[Seantastic]

It gave me an error (formular contains errors)

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)

 

[Seantastic]

Super! This worked well! Thanks!!

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

 

[T. Valko]

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)

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Super! This worked well! Thanks!!







- Show quoted text -