Look up array: ignoring values in between parentheses

[E1 calling]

Hello,

When using the vlookup formula (or alternatively a combination of the index and match formula) what should I add to the formula if the look up array contains:
- values in between parenthesis
- hyvens
All these values should be ignored in the vlookup.

Example of the look up array:
29 (+1)
17 (+1)
4 (-1)
2
12
-
1
3
11 (+1)
13 (-2)
2
1
21 (-7)

The values which matter are:
29
17
4
2
12
0
1
3
11
13
2
1
21

Any takers?

All your help will be highly appreciated.

Many thanks.

 

[Robert Mika]

There is probably not match you can do wth the lookup array but you can create a new column next to the existing one and trim the values in paranthesis:
=IF(ISNUMBER(FIND("(",A1)),TRIM(LEFT(A1,FIND("(",A1)-1)),A1)

Is the hyphen causing any problem in your array?

 

[Matty]

Avoiding the helper column, you could try:

=INDEX(E$2:E$14,MATCH(IF(ISNUMBER(A2),A2,IF(A2="-",0,LEFT(A2,FIND(" ",A2)-1)+0)),D$2:D$14,0))

Where E2:E14 contains the values to be returned and D2:D14 is the lookup_array.

Matty

 

[E1 calling]

Many thanks for your feedback, very helpful!