Any Functioon available..?

[Guest]

Hi ,
I am having two SHEETS like below,
Sheet1:
A1:City Codes
201
202
301
350
360
....

Sheet2:
A1:CityCodes B1:Rates
101,102,20,201,350,450 0.565
200,202,205,500 0.800
300,301,320,340,360 0.958
.............................. .........

Here i need the result like this:
Sheet1:
A1:City Codes B1
201 0.565
202 0.800
301 0.958
350 0.565
360 0.968
.... ......

Thanks in advance for your reply.

 

[Guest]

Install this UDF

Function strfind(citycode)
strfind = 0
On Error Resume Next
strfind = Worksheets("Sheet1").Columns("A:A").Find(What:=citycode,
After:=[A1], LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row
End Function

and insert this function into cell B2 on sheet1:
=INDEX(Sheet2!B:B,strfind(A2))

Regards,
Stefi

„Avadivelan TCS†ezt írta:

 

[vezerid]

Hi,

maybe you can use the following *array formula*:

=INDEX(Sheet2!$B$2:$B$100,MATCH(TRUE,ISNUMBER(SEARCH(","&A2&",",","&Sheet2!$A$2:$A$100&",")),0))

As it is an array formula, commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

 

[Guest]

Dear Stefi,
When i check this below code, i got the result
citycode
201 0.565
202 0.8
301 0.958
350 0
360 0

why 350,360 shows "0" instead of 0.565 & 0.958....?

Install this UDF

Function strfind(citycode)
strfind = 0
On Error Resume Next
strfind = Worksheets("Sheet1").Columns("A:A").Find(What:=citycode,
After:=[A1], LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row
End Function

and insert this function into cell B2 on sheet1:
=INDEX(Sheet2!B:B,strfind(A2))

Regards,
Stefi

„Avadivelan TCS†ezt írta:

Hi ,
I am having two SHEETS like below,
Sheet1:
A1:City Codes
201
202
301
350
360
...

Sheet2:
A1:CityCodes B1:Rates
101,102,20,201,350,450 0.565
200,202,205,500 0.800
300,301,320,340,360 0.958
............................. .........

Here i need the result like this:
Sheet1:
A1:City Codes B1
201 0.565
202 0.800
301 0.958
350 0.565
360 0.968
... ......

Thanks in advance for your reply.

 

[Guest]

Hi Vezerid,
when i try this , i got the result : #N/A

 

[Bob Phillips]

It works fine, you need to array enter it as Kostis said.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks all. Its working fine.

It works fine, you need to array enter it as Kostis said.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi,
I tryed it but it gives me an error. Perhaps it's because i don't know what
"As it is an array formula, commit with Shift+Ctrl+Enter". What do we have to
do?


"vezerid" escreveu:

 

[Bob Phillips]

With an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Harlan Grove]

Bob Phillips wrote...

With an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

....

Maybe in the non-US versions of Excel help there's some mention of the
term 'commit', but in US versions there isn't any instance at all of
the term 'commit'.

To enter array formulas (note the verb: ENTER), one holds down both
[Ctrl] and [Shift] keys before pressing the [Enter] key. Imagine that!
An actual correspondence between the action, ENTER a formula, and the
same word, Enter, on the principal key for doing so. Y'all can shut me
up when you can point to a Commit key on any standard keyboard.

 

[Bob Phillips]

Y'all can shut me
up when you can point to a Commit key on any standard keyboard.

We'd be so lucky.