# help with excel formula

#### nathan663

##### Board Regular
I am trying to do a vlookup formula. I have 2 columns of data. the first is a column of 6 digit numbers. The second is a column of sixdigit numbers followed by -1.

for example. in column A i may have 123456, and in column B i may have 123456-1

I need a vlookup that ignores the -1 (it is not always -1, it could be -2, -3, -4 etc.)

this is what i tried... but it failed... any ideas?

=IF(ISNA(VLOOKUP(A2,(LEFT(6)),C:D,2)),"No Match",(VLOOKUP(A2,(LEFT(6)),C:D,2)))

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Peter_SSs

##### MrExcel MVP, Moderator
Not sure what column B has to do with the problem since it is not referred to in the formula. You may need to more carefully explain your data and layout. So this is a pure guess:

=IF(ISNA(VLOOKUP(LEFT(A2,6)+0,C:D,2,0)),"No Match",(VLOOKUP(LEFT(A2,6)+0,C:D,2,0)))

Replies
11
Views
78
Replies
1
Views
50
Replies
3
Views
39
Replies
2
Views
47
Replies
7
Views
40