Function, Formula or VBA please help

[LadyHarper]

I have a Spread Sheet Columns A through F

In another work book, I need to pull over information.
I need it to look at Cells A1 and compare this to column B in other book, and look at B1 and compare to column E in other book and give me a list of each line from F that meets those criteria's from the other book.

Please help.

 

[KRice]

If I follow your description, this is one approach using formulas. Here I have a source data workbook named AnotherWorkbook containing a sheet named sourcedata that holds information in columns A:F. Another workbook named LadyHarper contains a worksheet named Sheet1, and on Sheet1 the user enters lookup query inputs in cells A1 and B1. For illustration purposes, I considered cell A1 to hold a name and B1 to hold a month. Then using a formula, cell A1 on Sheet1 is matched to all entries in column B of worksheet sourcedata (in AnotherWorkbook), and cell B1 on Sheet 1 is matched to all entries in column E of worksheet sourcedata (in AnotherWorkbook). Only when both of those conditions match is the line entry in sourcedata considered a "match", and the corresponding value in column F of worksheet sourcedata is returned. Since there may be multiple matches, the formula is dragged down to make room for listing each of the matches.

AnotherWorkbook.xlsx
	A	B	C	D	E	F
1	Data1	Name	Data3	Data4	Month	DataColF
2	A	Al	1021	xyz	June	top
3	B	Beth	1022	abc	August	bottom
4	C	Charles	1023	def	September	left
5	D	Delilah	1024	ghi	March	right
6	E	Ed	1025	jkl	April	north
7	F	Tom	1026	mno	January	south
8	G	Beth	1027	pqr	May	east
9	H	Charles	1028	stu	October	west
10	I	Delilah	1029	vwz	March	over
11	J	Tom	1030	bcd	February	under
12	K	Ed	1031	efg	July	port
13	L	Tom	1032	hij	January	starboard
14	M	Tom	1033	klm	January	top
sourcedata

LadyHarper.xlsx
	A	B	C
1	Tom	January	matching column f
2			south
3			starboard
4			top
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(INDEX([AnotherWorkbook.xlsx]sourcedata!$F:$F,AGGREGATE(15,6,(ROW([AnotherWorkbook.xlsx]sourcedata!$B:$B))/(([AnotherWorkbook.xlsx]sourcedata!$B:$B=$A$1)*([AnotherWorkbook.xlsx]sourcedata!$E:$E=$B$1)),IF(ROWS(C$2:C2)<=COUNTIFS([AnotherWorkbook.xlsx]sourcedata!$B:$B,$A$1,[AnotherWorkbook.xlsx]sourcedata!$E:$E,$B$1),ROWS(C$2:C2)))),"")

 

[Peter_SSs]

@LadyHarper
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[LadyHarper]

If I follow your description, this is one approach using formulas. Here I have a source data workbook named AnotherWorkbook containing a sheet named sourcedata that holds information in columns A:F. Another workbook named LadyHarper contains a worksheet named Sheet1, and on Sheet1 the user enters lookup query inputs in cells A1 and B1. For illustration purposes, I considered cell A1 to hold a name and B1 to hold a month. Then using a formula, cell A1 on Sheet1 is matched to all entries in column B of worksheet sourcedata (in AnotherWorkbook), and cell B1 on Sheet 1 is matched to all entries in column E of worksheet sourcedata (in AnotherWorkbook). Only when both of those conditions match is the line entry in sourcedata considered a "match", and the corresponding value in column F of worksheet sourcedata is returned. Since there may be multiple matches, the formula is dragged down to make room for listing each of the matches.

AnotherWorkbook.xlsx
	A	B	C	D	E	F
1	Data1	Name	Data3	Data4	Month	DataColF
2	A	Al	1021	xyz	June	top
3	B	Beth	1022	abc	August	bottom
4	C	Charles	1023	def	September	left
5	D	Delilah	1024	ghi	March	right
6	E	Ed	1025	jkl	April	north
7	F	Tom	1026	mno	January	south
8	G	Beth	1027	pqr	May	east
9	H	Charles	1028	stu	October	west
10	I	Delilah	1029	vwz	March	over
11	J	Tom	1030	bcd	February	under
12	K	Ed	1031	efg	July	port
13	L	Tom	1032	hij	January	starboard
14	M	Tom	1033	klm	January	top
sourcedata

LadyHarper.xlsx
	A	B	C
1	Tom	January	matching column f
2			south
3			starboard
4			top
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(INDEX([AnotherWorkbook.xlsx]sourcedata!$F:$F,AGGREGATE(15,6,(ROW([AnotherWorkbook.xlsx]sourcedata!$B:$B))/(([AnotherWorkbook.xlsx]sourcedata!$B:$B=$A$1)*([AnotherWorkbook.xlsx]sourcedata!$E:$E=$B$1)),IF(ROWS(C$2:C2)<=COUNTIFS([AnotherWorkbook.xlsx]sourcedata!$B:$B,$A$1,[AnotherWorkbook.xlsx]sourcedata!$E:$E,$B$1),ROWS(C$2:C2)))),"")

So Column A on the other report has repetitive information and so does column E, so it is going to find these multiple times, and I need it to list everything it finds

 

[Peter_SSs]

So Column A on the other report has repetitive information

Did you mean column B? No mention of column A in the other workbook in post #1.

so it is going to find these multiple times, and I need it to list everything it finds

Isn't that what KRice's results do?


Since you have updated your details (thanks for that) you could also use the FILTER function which only needs to be entered into a single cell (C2 in my case)

AnotherWorkbook.xlsx
	A	B	C	D	E	F
1	Data1	Name	Data3	Data4	Month	DataColF
2	A	Al	1021	xyz	June	top
3	B	Beth	1022	abc	August	bottom
4	C	Charles	1023	def	September	left
5	D	Delilah	1024	ghi	March	right
6	E	Ed	1025	jkl	April	north
7	F	Tom	1026	mno	January	south
8	G	Beth	1027	pqr	May	east
9	H	Charles	1028	stu	October	west
10	I	Delilah	1029	vwz	March	over
11	J	Tom	1030	bcd	February	under
12	K	Ed	1031	efg	July	port
13	L	Tom	1032	hij	January	starboard
14	M	Tom	1033	klm	January	top
sourcedata

LadyHarper.xlsm
	A	B	C
1	Tom	January	matching column f
2			south
3			starboard
4			top
5
6
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER([AnotherWorkbook.xlsx]sourcedata!$F$2:$F$100,(([AnotherWorkbook.xlsx]sourcedata!$B$2:$B$100=A1)*([AnotherWorkbook.xlsx]sourcedata!$E$2:$E$100=B1)),"")
Dynamic array formulas.

 

[KRice]

So Column A on the other report has repetitive information and so does column E, so it is going to find these multiple times, and I need it to list everything it finds

I'm confused by your reply. Would you like to find all instances where cell A1 matches column B (stated in post #1) or where cell A1 matches column A (suggested in post #4)? The solutions proposed above find multiple matches where: 1) A1 matches column B, and 2) B1 matches column E. For each row where those conditions are both true, the corresponding information found in column F is returned. Since you have 365, Peter's implementation of the FILTER function would be the preferred approach.

 

[LadyHarper]

Did you mean column B? No mention of column A in the other workbook in post #1.



Isn't that what KRice's results do?


Since you have updated your details (thanks for that) you could also use the FILTER function which only needs to be entered into a single cell (C2 in my case)

AnotherWorkbook.xlsx
	A	B	C	D	E	F
1	Data1	Name	Data3	Data4	Month	DataColF
2	A	Al	1021	xyz	June	top
3	B	Beth	1022	abc	August	bottom
4	C	Charles	1023	def	September	left
5	D	Delilah	1024	ghi	March	right
6	E	Ed	1025	jkl	April	north
7	F	Tom	1026	mno	January	south
8	G	Beth	1027	pqr	May	east
9	H	Charles	1028	stu	October	west
10	I	Delilah	1029	vwz	March	over
11	J	Tom	1030	bcd	February	under
12	K	Ed	1031	efg	July	port
13	L	Tom	1032	hij	January	starboard
14	M	Tom	1033	klm	January	top
sourcedata

LadyHarper.xlsm
	A	B	C
1	Tom	January	matching column f
2			south
3			starboard
4			top
5
6
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER([AnotherWorkbook.xlsx]sourcedata!$F$2:$F$100,(([AnotherWorkbook.xlsx]sourcedata!$B$2:$B$100=A1)*([AnotherWorkbook.xlsx]sourcedata!$E$2:$E$100=B1)),"")
Dynamic array formulas.

Thank you both so very much. I was able to get the Filter one to work. and it is doing exactly what I need.

 

[KRice]

Excellent...thanks for the update. We're happy to help.

 

[LadyHarper]

One more question. I am getting this error message... Links to "otherdocument" was not recalculated before it was saved.

First you should know I used the formula you wrote to do multiple B and E searches for different requirements. and it works beautifully. But is someone changes the column E Cell, I need my spreadsheet to do an automatic update.

Any suggestions? I use the main document (other document) in Sharepoint, and have to use my document in the APP as when opened in sharepoint it disables the links.

 

[KRice]

The formulas used above assume that both workbooks are in the same folder. When you implemented the formula above, does the reference to the "otherdocument" change to reflect the path to that file's Sharepoint location, and then are you opening the file directly from its Sharepoint location?

Based on a request for help for the same issue that I found on another site (see below), resolving this error might be difficult.

Updating locally synced workbook links after the supporting file is changed in Sharepoint web app

We have a master summary excel spreadsheet which is linked to cells in supporting workbooks. The files are synced locally on my computer but saved on SharePoint. This way, other users make changes to the supporting workbooks using web access. Then, I open the summary excel spreadsheet saved...

excel.uservoice.com

As a work-around, although it adds a step, you might try copying that most recent version of otherdocument from Sharepoint into your local working folder where the "LadyHarper" workbook (i.e., what you referred to as "my document") is located.