Try this:

=IF(A2="","",IF(ISNA(MATCH(A2&"*",Sheet2!B$2:B$16, 0)),0,INDEX(Sheet2!A$2:A$16,MATCH(A2&"*",Sheet2!B$ 2:B$16,0))))

Copy down as needed.

--
Biff
Microsoft Excel MVP


"nakliwala" wrote in message
ups.com...
Hi,

I am currently trying to use a VLOOKUP function in one worksheet that
refers to a table on the another worksheet in the same Excel
workbook. I havent used VLOOKUP functions in awhile. The VLOOKUP
function in sheet A should look through the 'SGL' column in sheet B
and compare it to the values in sheet A under the column SGL


Sheet A:

SGL Account Fund Group Bureau cod Partner Code ROC
1310
'vlookup goes here'
1310
1410
2110
2980


3102
3103
3103


5200
5200
5720
5720
5730
5755








This is what the reference table data looks like(Sheet B):
Category SGL
1 1610R
1 2530P
1 1611R
1 2531P
1 1612R
1 2532P
1 1613R
1 2533P
1 1618R
1 1620R
1 2540P
1 1621R
1 1622R
1 1623R
1 1630R

The vlookup needs to
1) compare the two SGL columns after 'truncating the R or P) and if
any values from sheet B match sheet A after truncating the ending
letter (R or P) then it should bring over the category from sheet B
and show that value in Sheet A under ROC column. Also, if there are
gaps in the SGL column from sheet A(as you can see above in sheet A)
then the ISNA function should output a value of 0 in the Roc column
for that row. Any suggestions/help will be greatly appreciated.


Thank you!