Search for value from array in cell return value if found

[stuartnico]

Column A - Sheet 2 ARRAY

1:2

1:4

1:5

1:6

1:8

1:10

1:12

1:20

etc etc

<tbody>
</tbody>

Column A	Column B
Tamiya 1:6 Tank	Formula search A for any in Sheet 2 list return if found = 1:6
Eduard 1:12 Plane	1:12
Revell 1:8 Plane	1:8

<tbody>
</tbody>

I suspect index and match are the way to go but no matter how I try I can't seem to work it out.

 

[stuartnico]

I've any number of ways to search for a value on its own but not yet a list of values and which value matches either to return the value found or a row no.

 

[XOR LX]

Hi.

=LOOKUP(1,0/SEARCH(Sheet2!$A$1:$A$8,A1),Sheet2!$A$1:$A$8)

Regards

 

[James006]

Hello,

Is there something preventing from using : =INDEX(Sheet2!A:A,MATCH(A1,Sheet2!A:A,0)) ...???

 

[stuartnico]

ADVERTISEMENT

Hi.

=LOOKUP(1,0/SEARCH(Sheet2!$A$1:$A$8,A1),Sheet2!$A$1:$A$8)

Regards

Nearly works for example is search a cell and came across 1:35 said from the list it was 1:3 not 1:35 needs to be an exact match.

 

[stuartnico]

Hello,

Is there something preventing from using : =INDEX(Sheet2!A:A,MATCH(A1,Sheet2!A:A,0)) ...???

On mine when I use MATCH(A1,Sheet2!A:A,0) for example I would hope to a return a row all I get is #N/A so nothing is passed onto INDEX

 

[stuartnico]

ADVERTISEMENT

Nearly works for example is search a cell and came across 1:35 said from the list it was 1:3 not 1:35 needs to be an exact match.

Ignore me I'm a muppet I didn't expand the column list to cover all in the list

 

[James006]

Correction to be tested on the Match portion ... MATCH("*"&A1&"*",Sheet2!A:A,0)

 

[XOR LX]

Nearly works for example is search a cell and came across 1:35 said from the list it was 1:3 not 1:35 needs to be an exact match.

Apologies. Yes - this is a potential issue.

You should be ok if you ensure that your Sheet2 list is in ascending order. If you can't guarantee this, then you will need to use a small modification, viz:

=LOOKUP(1,0/SEARCH(" "&Sheet2!$A$1:$A$8&" "," "&A1&" "),Sheet2!$A$1:$A$8)

Regards