how to apply lookup or any appropriate formula

[akshay6s]

hi,
we sent fabric rolls for different kinds of processing like dyeing, washing etc to different processors. so i am making a file by which i want to know that which processor is giving more processed fabric to us in a particular week.

in sheet1 column F is the rolls sent date column. column H is of processor's name column. column s is of process column.
column X is of processed rolls received column. and column AB to AE are meters and weight sent and received back columns respectively.

in sheet2! B1:F1 there ar
e dates and party name. i want that result should be according to only that given criteria. but this is showing all processors name in the given week. while i want to see only singhania finishers rolls sent (rolls sent criteria are B1:D1) and received back details. (for rolls received back date criteria are sheet2! C1, E1:F1

[Bernie Deitrick]

Convert the data on your sheet 1 to a table - I will assume that it is named Table1 - and on sheet two insert a column of formulas like this, entered into one cell using Ctrl-Shift-Enter instead of just enter:

=IFERROR(SMALL(IF(Table1[Party name ]=$C$1,IF(Table1[Date]>=$D$1,IF(Table1[Date]<$E$1,ROW(Table1[Party name])))),ROW(A1)),"")

I'm not sure what date ranges you are actually interested in.....

Then copy that cell down until it returns blanks. Then use those row numbers to extract information from Sheet1 using formulas like this:

=INDEX(Sheet1!E:E, cell address with the above formula), like

=INDEX(Sheet1!E:E, $H4)

copied down.

Or just filter the table on sheet 1 to show the values of interest in the appropriate columns.....