Filter pc-names (v-lookup?)

Hi,

I have an Excel with 2 sheets. Each sheet has a list of pc-names (in a column).

First sheet has all pc-names with owner, department etc. Second sheet contains pc-names which needs to be matched to first sheet. In other words: we need to know which pc (of second sheet) belong to who (data on first sheet).

Hope this is clear. Plase advise.


J.
janhoedtAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
The two formula you need are:

=IF(COUNTIF('ALL PC''s and their owners'!E:E,A6)>0,INDEX('ALL PC''s and their owners'!$C$2:$C$999,MATCH(A6,'ALL PC''s and their owners'!$E$2:$E$999,0)),"Not in list")

and

=IF(COUNTIF('ALL PC''s and their owners'!E:E,A6)>0,INDEX('ALL PC''s and their owners'!$D$2:$D$999,MATCH(A6,'ALL PC''s and their owners'!$E$2:$E$999,0)),"Not in list")

Plase see attached.
28481079lookup-sample.xlsx
0
 
SteveCommented:
Could you post a simple example file in a format similar to your file.

It will be easier to create the required formula with this.

As you may need VLOOKUP or an INDEX&MATCH.

ATB
Steve.
0
 
SteveCommented:
Attahced is a ver simple example of a VLOOKUP.
Having your file layout will make life a lot easier to get a tailored solution.
C--Users-shall-Desktop-Vlookup-Example.x
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
helpfinderIT ConsultantCommented:
basically VLOOKUP, but it depends on the data structure. Do you have, in 1st sheet, pc names in separate cell or it is combined with other data in one cell?
Check my sample how to use VLOOKUP applied to your request
sample.xlsx
0
 
janhoedtAuthor Commented:
0
 
helpfinderIT ConsultantCommented:
Based on your screenshots it is possible.
As I understand it yoi will check if hostname in tab2 (column A) exists in tab1 (column E).
Result of this comparison could be in column B of tab2. Do you want to match some value from tab1 as result or som text like OK and MISSING or?
0
 
SteveCommented:
OK, looking at the two immages you will need to use INDEX and MATCH together rather than a VLOOKUP.
This is due to the computer name looked up being in a more right column than the name being retreived.

The formula would look like:
=INDEX(Sheet1!$C$2:$C$999,MATCH(Sheet2!A3,Sheet1!$E$2:$E$999,0))
(the 999 may need to be increased or decreased based upon the number of rows on sheet 1)

I have also written the following, which checks for existence of the Computername in the list before doing the search:
=IF(COUNTIF(Sheet1!E:E,Sheet2!A2)>0,INDEX(Sheet1!$C$2:$C$999,MATCH(Sheet2!A3,Sheet1!$E$2:$E$999,0)),"Not in list")

The attached file show these as working examples.
Hoefully this will get you in the right direction.

ATB
Steve.
C--Users-shall-Desktop-IndexMatch.xlsx
0
 
janhoedtAuthor Commented:
Thanks! But I'm not fully getting it.
In att. the Excelsheet as example. Goal is to match  the sheet "pc's to be migrated"  to their owner (=sheet "ALL pc's and their owners).
lookup.xlsx
0
 
janhoedtAuthor Commented:
I hope I was clear in last update. Hereby a screenshot from what I'm trying to achieve.
lookup.jpg
0
 
helpfinderIT ConsultantCommented:
no problem to do it like you want, but for VLOOKUP you should have lookup values in tab1 in front of data you want to enter when positive match. That´s why I moved column with hostnames (E) infron of Account name and department.
See sample in attachment
lookup-sample.xlsx
0
All Courses

From novice to tech pro — start learning today.