with the combination of two selections get all the values pertaining to them in excel

[tpsdas]

Excel 2016 :
"PartData" sheet stores data and data is fetched from here. Print Report is the sheet where I select Emp No & Set No and get the data automatically in A23 to F28.
When I select Emp no & Set No (A3 & B3) , it automatically fetches Previous Set No. (in D22) & Count of Lines (in F22 - number of lines the combination of emp no & set have) from the PartData Sheet.

I need help on the followings : With the combination of Emp no (A4 ) and Previous Set No.( D22) and I want to get values of
Previous Items (the data will come from PartData: Column D:D),
Previous items Make (the data will come from PartData: Column J:J),
Previous Expense (the data will come from PartData: Column H:H)

A sample file (Trial.xls) is attached for your kind perusal. Please help me.

[Limor_OP]

Hi,
so what would be the previous item / set / make / expense in your example?

[tpsdas]

example:
a) if you select empNo: 20334 and set no:2, I am expecting the details of set no 1 (column Q:Q) pertaining to empno: 20334 (from column G, J, H from Partdata sheet to appear in A24: D28 of Print Report sheet)
b)if you select empNo: 20336 and set no: 4, I am expecting the details of set no 3(column Q:Q) pertaining to empno: 20336 (from column G, J, H from Partdata sheet to appear in A24: D28 of Print Report sheet)
Kindly refer the attachment in the original post .

[Limor_OP]

Hi,
Try this in A24:

Please Login or Register  to view this content.

In C24:

Please Login or Register  to view this content.

In D24:

Please Login or Register  to view this content.

[tpsdas]

Thanks a lot for the help. It works.
But i did not understand the formula : This area : AGGREGATE(15,6, and ROWS($H$1:J1)... could you kindly explain

[Limor_OP]

Hi,
The Aggregate function is structured this way:
AGGREGATE(function_num, options, array, [k])

"The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. A total of 19 operations are available, and the operation to perform is specified as a number
The second argument, options, controls how AGGREGATE handles errors and values in hidden rows"

In this specific formula I used the "SMALL" operation that is represented by the number "15" , and in options field I chose #6 which stands for "ignore error values".
In my array I have constricted the formula to provide only these lines that match 2 conditions:

1. PartsData!$Q$1:$Q$105='Print Report'!$D$22
2.PartsData!$D$1:$D$105='Print Report'!$A$4

The array would result in a bunch of errors where the conditions dont meet, and lines 100, 103 where the conditions do meet.

The aggregate(15,6 formula would provide the information in the 1st smallest line (line 100), and then the second smallest line (103), and if there were additional lines then so on, the 3rd and the 4th.

ROWS($H$1:H2) would stand for the X smallest number, in this case - 2nd smallest value in the array.
If you drag the formula down then it will become ROWS($H$1:H3) that signifies the 3rd smallest ,and so on.

You can read here more about the aggregate function:
https://exceljet.net/excel-functions...egate-function

[tpsdas]

Thanks a lot belinda200 . It is clear to me. Once again thanks a lot for all the helps

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Limor_OP]

You'e welcome tpsdas. Glad it helps.