successive matched via vlookup

[pete80]

Hello,

How can I list all successive matched values based on a vlookup?

Additonally, there is an additional criterium. See attachement.

Can you please help?

[wk9128]

Cell A2 Array formula , Drag down

Please Login or Register  to view this content.

If you want Column (E:G) list

formula

Cell A2 array formula , drag down and across

Please Login or Register  to view this content.

[pete80]

Thank you very much!

The number of rows are not fixed, so i want to look at the entire number of rows whether it are 113 or 200.

also

it has to search for a value that contains L-P-M1. So also look for L-P-M14 and L-P-M1-A3

[JeteMc]

The .xlsx file format was introduced with the 2007 version of Excel, so I suspect that you are using a version newer than the 2003 listed in your profile.
This makes a difference in the functions and/or features that might be used to resolve your issue.
Please update your profile when you post a reply.

[wk9128]

Try worksheet name : Blad1
Cell A2 array formula , Drag down

Please Login or Register  to view this content.

[alansidman]

Assuming you are running at least XL2010. I offer up an alternative using Power Query.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Make your selection in A2. Click on the Refresh All Icon and the results will appear starting in A13.

[pete80]

this is not working

[alansidman]

1.

this is not working

Don't know who or what you are referring to.
2. Please update your profile. Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile indicates 2003 yet your sample workbook is for 2007 or later. Confusing.

[JohnTopley]

Try

=IFERROR(INDEX(Blad2!A$1:A$1000,AGGREGATE(15,6,ROW($A$1:$A$1000)/(LEFT(Blad2!$A$1:$A$1000,LEN(Blad1!$A$1))=Blad1!$A$1)/(Blad2!$C$1:$C$1000="Yes"),ROWS($1:1))),"")

assuming you are on 2010 or above

[kvsrinivasamurthy]

If you are having AGGREGATE function, In B2 copied down

Please Login or Register  to view this content.

Otherwise use this ARRAY formula. In C2 copied down

Please Login or Register  to view this content.

Result is the row numbers.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[protonLeah]

Using a dynamic named range over E:G,

Please Login or Register  to view this content.

Modified wk9128 from post #2:

Please Login or Register  to view this content.