very urgent : to write the value of next column of the same row

[pol]

I applied the following formula to find the matching and write the data of
the corresponding next cell.

Sheet1.
the column C:C has custom format 000-000 having the data like this
123-456 12
001-789 11
313-245 13
456-779 16
084-321 18

IF(ISNA(MATCH(D5,Sheet1!C:C,0)),"Not exist",Sheet1!D2).
the matching is working but after, it is not writing corresponding data
from D2 value. It will put the data from skipping two row. Please help

Active sheet
The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist.





With thanks
Pol

 

[Jacob Skaria]

With data in cell B1 of activesheet;apply the below formula in cell C1 and
copy down as required...Its just the reverse what you have asked in the other
post....

=IF(ISNA(MATCH(TEXT(MID(B1,4,10),"000-000"),Sheet1!C:C,0)),"Not",
INDEX(Sheet1!D,MATCH(TEXT(MID(B1,4,10),"000-000"),Sheet1!C:C,0)))

 

[pol]

IF(ISNA(MATCH(D2,Sheet1!C:C,0)),"Not exist",Sheet1!D2).

If active sheet row in Activesheet!D3 and matching row found in sheet1!C6,
So I want to write corresponding sheet1!D6. Please can you advice me. So I
want to chage the equation

IF(ISNA(MATCH(D2,Sheet1!C:C,0)),"Not exist",Sheet1!D2).
change to
IF(ISNA(MATCH(D2,Sheet1!C:C,0)),"Not exist",Sheet1!Foundrow).

Please can you advice me . That would be very help


that formula is working but only one problem