certain data pulling up in a range of data sets and wanting any info to pull up along it

[aama]

I want this to only pull up the only the digits starting from "1000……" in the example I gave in the excel shirt. As well, I want in the range of the data to pull up any other data that is stringed along with it....I know I can write vlookup....but that goes by each column that is set...instead I want it more dynamic and able to capture anything in the columns that are associated with the "1000……"


So I want the other coloumn's data to align when it pulls out the "1000….."

so for example when "1000739940 is pulled up so is the data info and des detail fancy black

Data set 1 data info des detail
1000739940 about the purchase new socks

Please let me know if anyone wants further clarification and again I appreciate everyone's help!!!!

 

[Chihiro]

See attached.

PowerQuery method:
1. Convert range to table.
2. In Data tab, load from Table
3. Convert "Data set 1" column to Text format
4. Filter for "Begins with" 1000
5. Load table to sheet2

You can add dynamic parameter (single criteria) using named range. If interested see method outlined in thread below.
http://chandoo.org/forum/threads/co...-another-based-on-criteria.30989/#post-184423

Formula method also included (J:L range).

You can also accomplish this by setting up advanced filter. Links to tutorials below.
http://www.excel-easy.com/examples/advanced-filter.html
http://www.contextures.com/xladvfilter01.html