CODEWITHSUNDEEP
excel-formula
DeasyTech
DeasyTech

Reputation: 1

Return a list of values based on criteria - Excel

I have a PRODUCT sheet with 500 products listed. In the same workbook, I have a DOCKET sheet - a copy of which is sent with each persons order. I'd like to be able to "Enable" any number of rows on the PRODUCT sheet, and have them appear on the DOCKET.

Product Sheet

With the formula I'm currently using, I have to sort the PRODUCT list so that any of the lines I've "Enabled" are in the first few rows and it is tedious - =IF(PRODUCTS!$A3="Enable",PRODUCTS!$C3,"")

I'd like to be able to "Enable" a row anywhere in the PRODUCT list and have the value in Column C appear on the DOCKET.

I think I need to slot 'INDEX' in there somewhere but can't for the life of me work it out. Anyone know what formula I should use?

Upvotes: 0

Views: 429

Answers (1)

Pavel_V
Pavel_V

Reputation: 1230

If the number of products is not very high you can use something like this (enter into A2 of DOCKET sheet)

=IFERROR(INDEX(PRODUCT!C:C,AGGREGATE(15,6,ROW(PRODUCT!A:A)/(PRODUCT!A:A="Enable"),ROW(A1))),"")

The main part is this

AGGREGATE(15,6,ROW(PRODUCT!A:A)/(PRODUCT!A:A="Enable"),ROW(A1))

which finds n-th lowest row number that has "Enable" in column A of Product sheet, where n is to tied to row number so it starts at 1 and increments by 1 each row.

Upvotes: 1

Related Questions