Match values in one list to multiple values in another list

[luiszyx]

Hello!
I am looking for some tips for a big table data! I hope someone can help me!
As you can see in the image I have a table were (from line 426 to 431) in the Colum B I have scientific names of some species, in the other columns (C,D,E, etc.) I have codes of the locations of the Colum B species.
What I am looking now, is some kind of way to calculate witch scientific species are present in every single code locations new list (B435, B436, etc).
Can you understand the point?

Thanks in advance!
Luís Amen
Capturar1.JPGCapturar2.JPG

[NBVC]

Try:

=IFERROR(INDEX($B$426:$B$431,SMALL(IF($C$426:$K$431=$B435,ROW($B$426:$B$431)-ROW($B$426)+1),COLUMNS($C$435:C$435))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across as far as needed to get all, and down.

Note: adjust ranges to suit in C435, then re-confirm with CSE keys before copying over and down.

[luiszyx]

First of all many thanks!

But I still can´t do it! My excel is in Portuguese version, does it matter?
My data is a bit bigger than if photos (trying to simplify)

I have used this formula!
=SE.ERRO(ÍNDICE($B$5:$B$431,MÍNIMO(SE($C$5:$DV$431=$B435,COLUNA($B$5:$B$431)-COLUNA($B$5)+1),COLUNAS($C$435:C$435))),"")

Can you understand?

[NBVC]

I think the Portuguese version would be:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

you have to hold the CTRL and SHIFT keys down and press ENTER. You will see { } brackets around the formula... then you can copy down/across as needed.

[luiszyx]

Thanks a lot! Problem solved!
Very very grateful!

Luis