Conditional Lookup

[Saif-ur-Rehman]

with reference to uploaded file, I need to lookup value vertically with condition and need to skip all value that does not fulfill the applied condition. With any solution, if available, I will not be doing following steps anymore..
1. Copy data to result column
2. Filter data (Does not begin with "PK")
3. Delete rows on screen / Filtered
4. Remaining data is the result required.

Looking for expert opinion.

Thanks. BR

 

[Khalid NGO]

Hello Saif, Good day and welcome to the forum

Try the following {array} formula in E4:

=INDEX($B$4:$B$15,SMALL(IF(LEFT($B$4:$B$15,2)="PK",ROW($B$1:$B$12)),ROW(A1)))

To be array entered with Ctrl+Shift+Enter and copied down.

If you aren't familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Regards,

 

[Saif-ur-Rehman]

Bundle of thanks. I hope its gonna work.

 

[bines53]

Hi Khalid NGO,

I thought you learned to use the function AGGREGATE

=INDEX($B$6:$B$15,AGGREGATE(15,6,ROW($B$6:$B$15)-5/(LEFT($B$6:$B$15,2)="PK"),ROW(A1)),0)

David

 

[Khalid NGO]

Bundle of thanks. I hope its gonna work.

Thanks for the feedback Saif.

I thought you learned to use the function AGGREGATE

Hi David,
David Loves Aggregate

I have seen you have provided a lot of beautiful combination with aggregate, I've learned much from those, but couldn't implemented yet as I have 2007 at work.

Regards,