showing multiple results with VLookup between two spreadsheets

[Holley]

Thanks again as always for all your past help! I come to you once again in hopes of resolving an Excel issue. I know it can be done… I just cannot figure it out. I have two spreadsheets. Spreadsheet 1 contains multiple PO#s in Col B (It does not provide me with the invoice #). Spreadsheet 2 contains invoice #s in col A and their corresponding PO # in Col B. What I am trying to figure out is how to use VLookup on Spreadsheet 1 to show me each occurrence of the invoice # that shares the same PO between the two spreadsheets. Maybe another formula would be better?

 

[James006]

Hi,
There are several possibilities ... Array Formula will perform what you expect :

How to Vlookup Multiple Values in Excel - ExcelDemy

Here, we will learn how to vlookup multiple values in Excel. Here we used the VLOOKUP function and other functions to solve problems.

www.exceldemy.com

 

[Holley]

Hi,
There are several possibilities ... Array Formula will perform what you expect :

How to Vlookup Multiple Values in Excel - ExcelDemy

Here, we will learn how to vlookup multiple values in Excel. Here we used the VLOOKUP function and other functions to solve problems.

www.exceldemy.com

Thanks for your quick reply. I may be overlooking, but I don't see where it is comparing the data from two separate workbooks.

 

[AhoyNC]

Since your invoice numbers are to the left of the PO's on sheet2 you would not be able to use VLOOKUP.
Assuming you could have multiply invoices per PO Try:

Book1
	A	B	C	D	E
1		PO,s	Invoices
2		PO1	IN11	IN13
3		PO2	IN12
4		PO3
5		PO4	IN14
6		PO5	IN15
7		PO6	IN16	IN18
8		PO7	IN17
9		PO8
10		PO9	IN19
Sheet1

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(Sheet2!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1)/(Sheet2!$B$2:$B$10=$B2),COLUMNS($C$1:C1))),"")

Sheet2

Book1
	A	B
1	Inv	PO,s
2	IN11	PO1
3	IN12	PO2
4	IN13	PO1
5	IN14	PO4
6	IN15	PO5
7	IN16	PO6
8	IN17	PO7
9	IN18	PO6
10	IN19	PO9
Sheet2

 

[frabulator]

If you are just wanting to pull the data (not needing it for a full vlookup function), you can try EZ VLookup. The free version lets you use the Search All function across multiple workbooks. It brings up all instances of that one search criteria.

-EDIT-
Though you can refrence different workbooks exactly the same way with Vlookup. Instead of clicking on a range in the current workbook, just click on a range in the second workbook when selecting where to search.

 

[Holley]

If you are just wanting to pull the data (not needing it for a full vlookup function), you can try EZ VLookup. The free version lets you use the Search All function across multiple workbooks. It brings up all instances of that one search criteria.

-EDIT-
Though you can refrence different workbooks exactly the same way with Vlookup. Instead of clicking on a range in the current workbook, just click on a range in the second workbook when selecting where to search.

Unfortunately, we cannot use other software, but this looks very interesting, thanks for sharing!

 

[Holley]

Since your invoice numbers are to the left of the PO's on sheet2 you would not be able to use VLOOKUP.
Assuming you could have multiply invoices per PO Try:

This looks to be exactly what I need. is this reading from two separate Spreadsheets? File A and File B? I'm not following the formula.

 

[James006]

Thanks for your quick reply. I may be overlooking, but I don't see where it is comparing the data from two separate workbooks.

In your title you mention "worksheets" ... in your latest message "workbooks" ...

Just be re-assured all formulas will produce the results you expect ... since where data is located is a matter of adequate address ...

 

[AhoyNC]

The example is on worksheets in the same workbook. If you have 2 different workbooks you just need to reference them in the address.
In the formula (ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1) this just returns the row numbers (1-9 in the example) in the workbook or sheet that has the invoices.
(Sheet2!$B$2:$B$10=$B2) returns a TRUE or FALSE if it finds a match for the PO.
So, together (ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1)/(Sheet2!$B$2:$B$10=$B2) they return the rows that match the PO.
AGGREGATE(15,6 tells the aggregate function to use the SMALL option (#15) and to ignore (#6) errors.
COLUMNS($C$1:C1) as you drag the formula across the columns it for return 1,2,3...etc for the SMALL function.
So, that whole thing will tell the INDEX which row numbers it should use to return the invoices #'s.
You will need to change the cell #'s and sheet or workbook reference to match your data.
You can just drag (not copy) the formula down and across as needed.

 

[Holley]

The example is on worksheets in the same workbook. If you have 2 different workbooks you just need to reference them in the address.
In the formula (ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1) this just returns the row numbers (1-9 in the example) in the workbook or sheet that has the invoices.
(Sheet2!$B$2:$B$10=$B2) returns a TRUE or FALSE if it finds a match for the PO.
So, together (ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1)/(Sheet2!$B$2:$B$10=$B2) they return the rows that match the PO.
AGGREGATE(15,6 tells the aggregate function to use the SMALL option (#15) and to ignore (#6) errors.
COLUMNS($C$1:C1) as you drag the formula across the columns it for return 1,2,3...etc for the SMALL function.
So, that whole thing will tell the INDEX which row numbers it should use to return the invoices #'s.
You will need to change the cell #'s and sheet or workbook reference to match your data.
You can just drag (not copy) the formula down and across as needed.

Not sure what I am doing wrong. I'm not getting any data. There should always be at least one invoice. Here is a snippet from File A Maybe it will help make more sense.

I put the formula in Q2 and dragged it over to U2 but retrieved no data.

Here is a snippet from File B that contains the invoice #s and PO#s.