Prev: Creating Appointment
Next: Find max with a lookup
From: pol on 27 Apr 2010 04:45
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
From: Jacob Skaria on 27 Apr 2010 05:07
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:D,MATCH(TEXT(MID(B1,4,10),"000-000"),Sheet1!C:C,0)))

--
Jacob (MVP - Excel)


"pol" wrote:

> 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
From: pol on 27 Apr 2010 05:22
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



"pol" wrote:

> 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
 | 
Pages: 1
Prev: Creating Appointment
Next: Find max with a lookup