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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
showing multiple results with VLookup between two spreadsheets
- Thread starter Holley
- Start date
1. Please show a copy of the actual formula you entered
2. Are these 2 worksheets in the same workbook or 2 different workbooks?
3. Shouldn't the formula be in P2 and then dragged over and down?
4. I'm assuming the PO# is formatted the same in both files (either both are text or number).
2. Are these 2 worksheets in the same workbook or 2 different workbooks?
3. Shouldn't the formula be in P2 and then dragged over and down?
4. I'm assuming the PO# is formatted the same in both files (either both are text or number).
Upvote
0
Excel Facts
Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Thanks so much for your help and patience!!
1. =IFERROR(INDEX([Sheet2]File2!$A$2:$A$19499,AGGREGATE(15,6,(ROW([Sheet2]File2!$C$2:$C$19499)-ROW([Sheet2]File2!$C$2)+1)/([Sheet2]File2!$B$2:$B$10=$A1),COLUMNS($Q$Q1!))),"")
2. They are in two different workbooks.
3. YES! P2 should be where the formula is.
4. Yes, both are formatted GENERAL
1. =IFERROR(INDEX([Sheet2]File2!$A$2:$A$19499,AGGREGATE(15,6,(ROW([Sheet2]File2!$C$2:$C$19499)-ROW([Sheet2]File2!$C$2)+1)/([Sheet2]File2!$B$2:$B$10=$A1),COLUMNS($Q$Q1!))),"")
2. They are in two different workbooks.
3. YES! P2 should be where the formula is.
4. Yes, both are formatted GENERAL
Upvote
0
Your syntax is wrong.
I named this workbook File A
This workbook named File B (change to the names of your workbooks.
This link will explain the formula.
I named this workbook File A
| File A.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | P | Q | R | S | T | U | ||||||||
| 1 | PO # | Invoice Date | Tax/VAT | Payment | Business | Invoice 1 | Invoice 2 | Invoice 3 | Invoice 4 | Invoice 5 | Invoice 6 | ||||||||||||
| 2 | 12345 | 3/3/2023 | 316 | -15.8 | 0 | 0 | 0 | 0 | 300.20 | Company | 123 | 201 | 250 | ||||||||||
| 3 | 20056 | 3/4/2023 | 124 | 300 | |||||||||||||||||||
Sheet1 | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P2:T3 | P2 | =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$6,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$6)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$6=$A2),COLUMNS($P$1:P1))),"") |
| U2:U3 | U2 | =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$4,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$4)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$4=$A2),COLUMNS($P$1:U1))),"") |
This workbook named File B (change to the names of your workbooks.
| File B.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Inv # | Purch Order | Inv Date | Inv $ | ||||
| 2 | 123 | 12345 | 8/3/2022 | 124.71 | ||||
| 3 | 124 | 20056 | 8/4/2022 | 124.71 | ||||
| 4 | 201 | 12345 | 8/5/2022 | 124.71 | ||||
| 5 | 300 | 20056 | 8/6/2022 | 125.71 | ||||
| 6 | 250 | 12345 | 8/7/2022 | 126.71 | ||||
Sheet1 | ||||||||
This link will explain the formula.
Upvote
0
Thank you! I am still not getting any data output after entering the formula. I will have to watch the video after hours, it is blocked here in the office. Hopefully, I can find my error. Here is a snippet after pasting the formulas as you provided.
Formula is File A, Cell P2:T3 =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$6,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$6)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$6=$A2),COLUMNS($P$1:P1))),"")
Formula in File A, Cell U2:U3 =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$4,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$4)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$4=$A3),COLUMNS($P$1:U2))),"")
Formula is File A, Cell P2:T3 =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$6,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$6)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$6=$A2),COLUMNS($P$1:P1))),"")
Formula in File A, Cell U2:U3 =IFERROR(INDEX('[File B.xlsx]Sheet1'!$A$2:$A$4,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$4)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$4=$A3),COLUMNS($P$1:U2))),"")
Attachments
Upvote
0
One thing I see is my formula in U2 has a wrong range for the example. The range should be A2 - A6 the same as the formula in P2. If you drag the formula in P2 down and across it should have the right ranges for the example. Since you are getting blanks there is an error in the formula or the data. Take the IFERROR part of the formula out to see what error you are getting. Also, if you use the "Evaluate Formula" option under the Formulas ribbon it should show where the error is.
Also, make sure you change the references to match your file names and ranges.. Another thing to check is the PO's in the 2 files. Even though you say they have general format. Check a cell in each file with the ISTEXT function. They both should return either TRUE or FALSE.
Formula without the IFERROR:
=INDEX('[File B.xlsx]Sheet1'!$A$2:$A$6,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$6)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$6=$A2),COLUMNS($P$1:P1)))
Also, make sure you change the references to match your file names and ranges.. Another thing to check is the PO's in the 2 files. Even though you say they have general format. Check a cell in each file with the ISTEXT function. They both should return either TRUE or FALSE.
Formula without the IFERROR:
=INDEX('[File B.xlsx]Sheet1'!$A$2:$A$6,AGGREGATE(15,6,(ROW('[File B.xlsx]Sheet1'!$A$2:$A$6)-ROW('[File B.xlsx]Sheet1'!$A$2)+1)/('[File B.xlsx]Sheet1'!$B$2:$B$6=$A2),COLUMNS($P$1:P1)))
Upvote
0
Still working with the formula but now I am getting #NUM error. I haven't had an opportunity to watch the video yet- unfortunately. There are 19499 rows in file B, I would think I would need to adjust the range to $A$2:$A$19499... etc?
Upvote
0
Upvote
0
Since you are getting an #NUM error would lead me to believe that the PO's in File A and File B are different (which would give all the #Div/0 errors you see.
Open a new wookbook
Copy and paste a PO# from File A into cell A1
Then copy and paste the same PO# from File B into cell B1
In cell C1 enter A1=B1 and my guess is it will show FALSE.
Two things to look for:
1. Are the PO# numeric in one file and text in the other (a cell formatted as General can have both numeric & text).
2. If both are text then look to see if one may have some leading or trailing spaces.
Open a new wookbook
Copy and paste a PO# from File A into cell A1
Then copy and paste the same PO# from File B into cell B1
In cell C1 enter A1=B1 and my guess is it will show FALSE.
Two things to look for:
1. Are the PO# numeric in one file and text in the other (a cell formatted as General can have both numeric & text).
2. If both are text then look to see if one may have some leading or trailing spaces.
Upvote
0
Solution
GOT DATA! YAY!! You are a lifesafer and GENIUS! THANK YOU SO VERY MUCH!!
Upvote
0
Similar threads
- Solved
