google-sheets

Google Sheets Getting data from cells in 1 column using multiple instances of text entry's from a different column

Im not the best at sheets but usually find a solution for my needs although far from the best way of doing things so i would like to make this 1 part a little easier than manual input

I've added an image to hopefully explain what im looking for

I want to search column K but use the data from column B and put that data in the correct place in N3:AA10, i've tried vlookup,match,small,large in various combinations but unable to figure this out.

Atm I have manually filled out N3:AA10 and would like to know if this could be automated from the data in Col B using Col k as a search ref

So for example N4 is GT1 car class..I want to find all GT1 car classes which is found in (col K) and place the car model on the same row (Col B) into N4:AA4

So in cell N4 look for the 1st instance "Gt1" in Column K, in this case its (K5).I want to use the car model Aston Martin DBR9 GT1 (B5) and enter that text into N4

Next in cell O4 look for the 2nd instance of "Gt1" in Column K, In this case its (K15) so i want to use the car model Chevrolet Corvette C6.R GT1 (B15) and enter that text into O4

=LOOKUP("gt1",K5:K,B5:B) gets me the 1st instance but how to get the 2nd,third instance is where im stuck

Sheet Image

Link to Sheet Tab Car Data

Solution

  • Use Filter to List All Data Accordingly

    You may use the following formula on cell N3:

    =BYROW(M3:M10,lambda(x,TRANSPOSE(INDEX(FILTER(B5:K,K5:K=x),,1))))

    If the list expands, you may change M3:M10 (which is the list of unique values of column k) accordingly.

    Output:

    Output

    References: