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

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]PC Name
[/TD]
[TD]Adobe
[/TD]
[TD]Project
[/TD]
[TD]Snagit
[/TD]
[/TR]
[TR]
[TD]PC001
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]PC002
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PC003
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (Installed Software List)

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]PC001
[/TD]
[TD="align: center"]PC002
[/TD]
[TD="align: center"]PC003
[/TD]
[/TR]
[TR]
[TD]Installed Software
[/TD]
[TD]Installed Software[/TD]
[TD]Installed Software[/TD]
[/TR]
[TR]
[TD]Adobe
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
Yes
Yes	Yes	Yes	Yes	Yes	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PC Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Adobe[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Project[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Snagit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]IE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Excel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Word[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PC001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PC002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PC003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Yes[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PC004[/TD]

</tbody>

Installed Software List​

Then you can use this formula on the second sheet:

	A	B	C	D	E
PC001	PC002	PC003	PC004
Installed Software	Installed Software	Installed Software	Installed Software
Adobe	Excel	Snagit	Adobe
Project		IE	Project
IE		Excel	Snagit
Word			IE
			Excel
	Word

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Table format​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH="class: cms_table"][/TH]
[TH="class: cms_table"]A[/TH]
[TH="class: cms_table"]B[/TH]
[TH="class: cms_table"]C[/TH]
[TH="class: cms_table"]D[/TH]
[TH="class: cms_table"]E[/TH]
[TH="class: cms_table"]F[/TH]
[TH="class: cms_table"]G
[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]PC Name[/TD]
[TD]Adobe[/TD]
[TD]Project[/TD]
[TD]Snagit[/TD]
[TD]IE[/TD]
[TD]Excel[/TD]
[TD]Word[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]PC001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]PC002[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]PC003[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]PC004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

[TABLE="class: grid"]
<tbody>[TR]
[TH="class: cms_table"][/TH]
[TH="class: cms_table"]A[/TH]
[TH="class: cms_table"]B[/TH]
[TH="class: cms_table"]C[/TH]
[TH="class: cms_table"]D
[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]PC001
[/TD]
[TD="align: center"]PC002[/TD]
[TD="align: center"]PC003[/TD]
[TD="align: center"]PC004[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Installed Software[/TD]
[TD="align: center"]Installed Software[/TD]
[TD="align: center"]Installed Software[/TD]
[TD="align: center"]Installed Software[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Word
[/TD]
[TD]Adobe
[/TD]
[TD]Project
[/TD]
[TD]Word
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Excel
[/TD]
[TD]Word
[/TD]
[TD]Excel
[/TD]
[TD]Excel
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]IE
[/TD]
[TD]Snagit
[/TD]
[TD]Word
[/TD]
[TD]Adobe
[/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]PC Name[/TD]
[TD="bgcolor: #FAFAFA"]Adobe[/TD]
[TD="bgcolor: #FAFAFA"]Project[/TD]
[TD="bgcolor: #FAFAFA"]Snagit[/TD]
[TD="bgcolor: #FAFAFA"]IE[/TD]
[TD="bgcolor: #FAFAFA"]Excel[/TD]
[TD="bgcolor: #FAFAFA"]Word[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]PC001[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]PC002[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]PC003[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]PC004[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]

</tbody>

Installed Software List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(ISNUMBER(MATCH(B$1,INDEX(Sheet3!$A$1:$Z$100,0,MATCH($A2,Sheet3!$A$1:$Z$1,0)),0)),"Yes","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!