Vlookup or match assistance

Wecks

New Member
Joined
Aug 11, 2018
Messages
5
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
ABCD
1Machine IDMachine NameIP AddressUser Name
200001PC00110.0.0.1Joe Bloggs
300002PC00210.0.0.2Jane Smith
400003PC00310.0.0.3
500004PC00410.0.0.4John Wick
MACHINES
Cell Formulas
RangeFormula
D2=IFERROR(INDEX(USERS!A$2:A$4,MATCH(B2,USERS!B$2:B$4,0)),"")



Book1
AB
1User NameMachines Name
2Joe BloggsPC001
3Jane SmithPC002
4John WickPC004
USERS


Formula copied down.

I'm using a "" (Blank) when there's no match, change that to what you want.
 
Upvote 0
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
 
Upvote 0
You're welcome, welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,198
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top