Vlookup or match assistance

[Wecks]

This is probably very simple so I apologise in advance if it is but I have to work sheets. I would like to pull data from a column in one worksheet where values March in both. I have tried the usual lookups but I either get the incorrect column value or it will not match and simply bring me back the corresponding value in the similar cell it is looking up in the other worksheet so it is not performing a match.

More details of specifics below I am using a simple example as the formula I can modify to work on my larger set of spreadsheets once I work out how to get this done.

I have 2 Worksheets in one Excel Document.

Sheet one is names MACHINES

Column values are

MachineID
MachineName
IPAddress

Example data below

00001 PC001 10.0.0.1
00002 PC002 10.0.0.2
00004 PC004 10.0.0.4


Sheet Two is USERS

UserName
MachinesName

Example Data Below

Joe Bloggs PC001
Jane Smith PC002
John Wick PC004


I want to add one additional column into the MACHINE Worksheet called UserName.
I would like to do a formula (I am guessing a vlookup) that simply adds the UserName value based on where the MachineNames match.

For the sake of this example let’s assume that A1 contains the column headings.

I would like to end up with the following in the Machine Worksheet.


00001 PC001 10.0.0.1 Joe Bloggs
00002 PC002 10.0.0.2 Jane Smith
00004 PC004 10.0.0.4 John Wick


Now it is not that i have not tried to do this I have tied a numerous vlookups to no avail, i have googled what i could and not been successful. Any assistance would be a great help this is driving me mad.

 

[jtakw]

Hi,

VLOOKUP looks to the Right for the match, your data for the match is on the Left, so it won't work here, we need to use INDEX/MATCH:

Book1
	A	B	C	D
1	Machine ID	Machine Name	IP Address	User Name
2	00001	PC001	10.0.0.1	Joe Bloggs
3	00002	PC002	10.0.0.2	Jane Smith
4	00003	PC003	10.0.0.3
5	00004	PC004	10.0.0.4	John Wick
MACHINES

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(USERS!A$2:A$4,MATCH(B2,USERS!B$2:B$4,0)),"")

Book1
	A	B
1	User Name	Machines Name
2	Joe Bloggs	PC001
3	Jane Smith	PC002
4	John Wick	PC004
USERS

Formula copied down.

I'm using a "" (Blank) when there's no match, change that to what you want.

 

[offthelip]

You can't use Vlookup because the column of data you ar trying to pick up is toi the left of the "match" column and Vlookup wonlt allow you to do that.
However you can do with index and match as below:
=INDEX(Users!A$2:A$4,MATCH(Users!B$2:B$4,Machines!B2))
Change the address on the arrays to match your complete columns and then copy the formula down

 

[Wecks]

I used this one and it was spot on. Thankyou.

 

[jtakw]

You're welcome, welcome to the forum.