help with excel formula

nathan663

Board Regular
Joined
Dec 3, 2008
Messages
153
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)))
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,231
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,979
Messages
5,526,023
Members
409,675
Latest member
audeser

This Week's Hot Topics

Top