# Search for value from array in cell return value if found

#### stuartnico

##### New Member
 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.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### stuartnico

##### New Member
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

##### Well-known Member
Hi.

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

Regards

#### James006

##### Well-known Member
Hello,

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

#### stuartnico

##### New Member
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

##### New Member
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

##### New Member
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

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

#### XOR LX

##### Well-known Member
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

Replies
2
Views
298
Replies
4
Views
600
Replies
5
Views
1K
Replies
15
Views
779
Replies
4
Views
4K

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back