Multiple values with same critiria

[xsmurf]

Hi,

I hope you guys can help me with a problem if have.

I have a code that looks up values in a separate excel file, this code works well (modified the location to the location

=IFERROR(INDEX('C:\Users\123\Desktop\Excel WIP\[Production Report.xlsm]Production Reports'!$D$2:$D$2002,MATCH(B16&C16,'C:\Users\123\Desktop\Excel WIP\[Production Report.xlsm]Production Reports'!$C$2:$C$2002&'C:\Users\123\Desktop\Excel WIP\[Production Report.xlsm]Production Reports'!$F$2:$F$2002,0)),"")

My problem is that sometimes there will be an extra row with the same lookup values, however my lookup code only shows one value and not the next one.
I would like to show both values with the same lookup values.
So if row 9 & row 10 have different values but the lookup values are the same I would like to see both values. ( in cells underneath each other)

Can somebody help me to adjust my code so it shows all the values.

Thank you so much for the help.

 

[Toadstool]

Hi XSmurf

Here's the Production Reports

XSmurf.xlsx
	C	D	E	F
1	C	D		F
2	W	Mole		T
3	Z	Cat		X
4	A	Dog		B
5	Z	Goat		X
6	A	Sheep		B
7
8	A	Badger		B
9	Z	Vole		X
10	A	Fox		B
11	Z	Horse		X
12
Production Reports

Here's the retrieval into cells underneath:

XSmurf.xlsx
	A	B	C
15	Results
16	Cat	Z	X
17	Goat
18	Vole
19	Horse
20
1st

Cell Formulas
Range	Formula
A16:A20	A16	=IFERROR(INDEX('Production Reports'!$D$2:$D$2000,AGGREGATE(15,6,ROW('Production Reports'!$C$2:$C$2000)-ROW('Production Reports'!$C$1)/(('Production Reports'!$C$2:$C$2000=$B$16)*('Production Reports'!$F$2:$F$2000=$C$16)),ROW()-ROW($A$15))),"")

 

[xsmurf]

Hi ToadStool,

Thank you for replying. I need to say sorry because I read my post back and I did not provide the proper info,

The tab Production Reports is the tab where my info is in.
This tab has 40 columns, and rows are added 3 times a day and sometimes 4 or 5 times a day.

In my search sheet I retrieve info from the 40 columns in the production tab.
The 2 values I look up are in column B & C on the search sheet.
Column B has a date in it and column C has a reference number, these values can change per row.

The other columns in the search tab will show the date that belongs to the search values on that row.
Sometimes the search values are the same for 2 rows but the values in the rest of the columns is different.

In my search tab I would like to see those 2 rows with the different data in it.
The search info will give me around 42 rows in total depending on the data during the week.

If needed I can try to make a mini sheet but I will have to remove some sensitive information first.

hope you can still help me.

Thank you in advance

 

[Toadstool]

I'm not understanding so yes, mini-sheets would be useful with your Production Reports sheet, but more importantly your search sheet with a worked example of output.

You should also update your profile with your Excel version as I use Excel 2016 but others may be able to provide a better solution if you have Excel 2019, 2021 or 365.

 

[xsmurf]

link to example sheet

Please see the link for the example sheet.

Also I am using Microsoft 365

 

[Toadstool]

Your first post said "I would like to see both values. ( in cells underneath each other)" but that would involve inserting rows as B & C already contain data so for that you'd need VBA.

I can rebuild columns B & C into new columns with the new rows included but for my Excel 2016 that would need me to add helper columns. Another 365 forum member may be able to provide a better solution.

The row 28 you highlight in yellow doesn't have a duplicate Jan 16, 2022 and C7 in the Production Reports, only row 20 for Jan 13, 2022 and B4 has a duplicate.

Another approach would be to build columns B and C from scratch. It appears that your first B date is that specified in A4 and then you have three entries for each day (an A, a B and a C" suffixed by the day of the week. There would be additional entries for each day where multiple entries for a Date, column C are found in the Production Reports. Let me know if you want me to try that.

 

[xsmurf]

Your first post said "I would like to see both values. ( in cells underneath each other)" but that would involve inserting rows as B & C already contain data so for that you'd need VBA.

I can rebuild columns B & C into new columns with the new rows included but for my Excel 2016 that would need me to add helper columns. Another 365 forum member may be able to provide a better solution.

The row 28 you highlight in yellow doesn't have a duplicate Jan 16, 2022 and C7 in the Production Reports, only row 20 for Jan 13, 2022 and B4 has a duplicate.

Another approach would be to build columns B and C from scratch. It appears that your first B date is that specified in A4 and then you have three entries for each day (an A, a B and a C" suffixed by the day of the week. There would be additional entries for each day where multiple entries for a Date, column C are found in the Production Reports. Let me know if you want me to try that.

Sorry the late reply, had some personal issues.

I still haven't found a answer to my problem.
If you can and have time could you try your solution.

Unless somebody has a office 365 solution.

Anyway I would like to thank you (or who else will help out) for the assistance, I really appreciate it.

 

[xsmurf]

Sorry the late reply, had some personal issues.

I still haven't found a answer to my problem.
If you can and have time could you try your solution.

Unless somebody has a office 365 solution.

Anyway I would like to thank you (or who else will help out) for the assistance, I really appreciate it.

I am using this code now

=IFERROR(INDEX('\\drive01\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$D$2:$D$2000,MATCH(B19&C19,'\\drive01\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$C$2:$C$2000&'\\drive01\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$F$2:$F$2000,0)),"")

I need to retrieve the values from a different workbook, that match the criteria based in my other workbook in cell B19&C19

 

[xsmurf]

I need to retrieve the values from a different workbook, that match the criteria based in my other workbook in cell B19&C19

I mean multiple values of 2 criteria in a different workbook

 

[xsmurf]

Hi,

I am still struggling to find a proper code to find multiple results, and I really hope someone can help me.
The code I am using now:

=IFERROR(INDEX('Production Reports'!$D$2:$D$2002,MATCH(B27&C27,'Production Reports'!$C$2:$C$2002&'Production Reports'!$F$2:$F$2002,0)),"")

This will only show me 1 result, but if I have 2 results below each other I would like to see that result too.
The results will be shown beneath each other.
I am using this code in 1 workbook to look in a different sheet, but this code will also be used in a different workbook that retrieves values from different workbooks.

Thank for the help in advance.