can vlookup jump to different table array on fill down

[FuzzyExcel]

Hi all,

Thank you in advance for any help offered. I don't think what I'm trying to do is even possible.
So I have a vlookup that looks for a software installed on a PC and if the software is installed it returns Yes and if not installed it returns No. On sheet 1 I have the PC names listed in column A and the software name I'm looking for is listed in the table header going from B to G. When I enter the following formula in B2 and fill across it works brilliantly =IF(ISNA(VLOOKUP(B1,'Installed Software List'!$E$3:$E$98,1,FALSE)),"No","Yes")
But what i would also like to do is be able to fill down and the table array change from $E$3:$E$98 to $f$3:$f$98 and so on.
Sheet 1 looks like this

A	B	C	D
PC Name	Adobe	Project	Snagit
PC001	Yes	Yes	No
PC002
PC003

<tbody>
</tbody>

Sheet 2 (Installed Software List)

E	F	G
PC001	PC002	PC003
Installed Software	Installed Software	Installed Software
Adobe
Project
IE

<tbody>
</tbody>

Again I don't think this is possible using the formula i'm using. I'm not that good with Excel so any help/advice is greatly appriciated.

Thank you.

 

[Eric W]

Welcome to the forum.

I'm not entirely clear on the details, but if your first sheet looks like:

	A	B	C	D	E	F	G
1	PC Name	Adobe	Project	Snagit	IE	Excel	Word
2	PC001	Yes	Yes	No	Yes	No	Yes
3	PC002	No	No			Yes
4	PC003			Yes	Yes	Yes
5	PC004	Yes	Yes	Yes	Yes	Yes	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Installed Software List​

Then you can use this formula on the second sheet:

	A	B	C	D	E
1	PC001	PC002	PC003	PC004
2	Installed Software	Installed Software	Installed Software	Installed Software
3	Adobe	Excel	Snagit	Adobe
4	Project		IE	Project
5	IE		Excel	Snagit
6	Word			IE
7				Excel
8				Word
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Table format​

Array Formulas Cell Formula A3 {=IFERROR(INDEX('Installed Software List'!$B$1:$G$1,SMALL(IF(INDEX('Installed Software List'!$B$2:$G$98,MATCH(A$1,'Installed Software List'!$A$2:$A$98,0),0)="Yes",COLUMN('Installed Software List'!$B$1:$G$1)-COLUMN('Installed Software List'!$B$1)+1),ROWS(A$3:A3))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You can drag the formula down and to the right as needed.

 

[FuzzyExcel]

Hi Eric,

Thank you so much for your quick response and help with this, I've been trying to work this out for the last week . I'm not sure if this formula will work for me. Basically what I'm trying to do is: I have a list of 400 PCs and I have a restricted software list of 11 software/applications. I need to find out if any of the 400 PC's have this restricted software installed on them. So I created sheet 1 and in Column A I put PC name and a list of the 400 PCs and then in B1,C1, D1 etc is the restricted software name. What I was hoping to do was put some sort of formula in B2 that I can drag down and to the right that returns Yes or No if the software is installed or not. In sheet 2 I've exported separate lists for each PC of the software they have installed so basically sheet 2 has a list in each column with the PC name and all software installed on that PC. I've tried using the vlookup but this only works for dragging across but not down. Would your formula work in sheet 1 pulling the data from sheet 2????? Hope this makes sense what I'm trying to do.

Sheet 1

	A	B	C	D	E	F	G
1	PC Name	Adobe	Project	Snagit	IE	Excel	Word
2	PC001
3	PC002
4	PC003
5	PC004

<tbody>
</tbody>

Sheet 2

	A	B	C	D
1	PC001	PC002	PC003	PC004
2	Installed Software	Installed Software	Installed Software	Installed Software
3	Word	Adobe	Project	Word
4	Excel	Word	Excel	Excel
5	IE	Snagit	Word	Adobe

<tbody>
</tbody>

Thanks again for your help I really appreciate it.

 

[Eric W]

Looks like I had it backwards. With the sheet layouts the same, try this

	A	B	C	D	E	F	G
1	PC Name	Adobe	Project	Snagit	IE	Excel	Word
2	PC001	Yes	Yes		Yes		Yes
3	PC002					Yes
4	PC003			Yes	Yes	Yes
5	PC004	Yes	Yes	Yes	Yes	Yes	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Installed Software List​

Worksheet Formulas Cell Formula B2 =IF(ISNUMBER(MATCH(B$1,INDEX(Sheet3!$A$1:$Z$100,0,MATCH($A2,Sheet3!$A$1:$Z$1,0)),0)),"Yes","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Change the ranges in the formula to match your sheet. Then you can drag it down and across as needed.

 

[FuzzyExcel]

Hi Eric,

Thank you, Thank you, Thank you. That worked perfectly. You are a genius. I would never have worked that out. You saved me hours of manually looking through lists of software.

Thank you so much

 

[Eric W]

Glad to help!