Hi there.

I have a table of the following style.

right now i tried to find some worksheet function that will take in a number
for "my id" or "your id" and then returned the corresponding number in "your
id" or "my id".

for a given "my id", it is relatively simple to retrieve the corresponding
"your id" via VLOOKUP("MY ID", "ARRAY RANGE"), as "my id" is only a single
number and sorted ascendingly.

I am struggling in geting the reverse to work. How can I retrieve the
corresponding "my id" is I were given a "your id" number?

my id your id
------------------------------------------
52177 9561
62532.01 8910
726589 90026, 8926


mnay thanks.

Use the MATCH function to find the row in your_id and embed this within
the INDEX function to retrieve my_id. When you use VLOOKUP the lookup
value must be in the left hand column of the table, so you can't use
this for what you want to do.

Hope this helps.

Pete

Thanks Pete,

The MATCH & INDEX works beutifully if both column only contains one number.

The remaining problem right now is my "your id" column might contain a fwe
number like: "8914, 8916"; MATCH just dont like it at all.


"Pete_UK" wrote in message
ups.com...
Use the MATCH function to find the row in your_id and embed this within
the INDEX function to retrieve my_id. When you use VLOOKUP the lookup
value must be in the left hand column of the table, so you can't use
this for what you want to do.

Hope this helps.

Pete

Assuming that A2:B4 contains your data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A2:A4,MATCH(TRUE,ISNUMBER(SEARCH(" "&D2&","," "&B2:B4&",")),0))

....where D2 contains the 'your id' of interest, such as 90026.

Hope this helps!

In article ,
"Kok Yong Lee" wrote:

Hi there.

I have a table of the following style.

right now i tried to find some worksheet function that will take in a number
for "my id" or "your id" and then returned the corresponding number in "your
id" or "my id".

for a given "my id", it is relatively simple to retrieve the corresponding
"your id" via VLOOKUP("MY ID", "ARRAY RANGE"), as "my id" is only a single
number and sorted ascendingly.

I am struggling in geting the reverse to work. How can I retrieve the
corresponding "my id" is I were given a "your id" number?

my id your id
------------------------------------------
52177 9561
62532.01 8910
726589 90026, 8926


mnay thanks.

Hi Domenic,

That does the trick! Excellent.

Thanks a million.


"Domenic" wrote in message
...
Assuming that A2:B4 contains your data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A2:A4,MATCH(TRUE,ISNUMBER(SEARCH(" "&D2&","," "&B2:B4&",")),0))

...where D2 contains the 'your id' of interest, such as 90026.

Hope this helps!

In article ,
"Kok Yong Lee" wrote:

Hi there.

I have a table of the following style.

right now i tried to find some worksheet function that will take in a
number
for "my id" or "your id" and then returned the corresponding number in
"your
id" or "my id".

for a given "my id", it is relatively simple to retrieve the
corresponding
"your id" via VLOOKUP("MY ID", "ARRAY RANGE"), as "my id" is only a
single
number and sorted ascendingly.

I am struggling in geting the reverse to work. How can I retrieve the
corresponding "my id" is I were given a "your id" number?

my id your id
------------------------------------------
52177 9561
62532.01 8910
726589 90026, 8926


mnay thanks.

Hi Domenic,

Any where I can find more information regarding your rather exotic usage of
'&' character in SEARCH(" "&D2&","," "&B2:B4&",")?

many thanks.

"Domenic" wrote in message
...
Assuming that A2:B4 contains your data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A2:A4,MATCH(TRUE,ISNUMBER(SEARCH(" "&D2&","," "&B2:B4&",")),0))

...where D2 contains the 'your id' of interest, such as 90026.

Hope this helps!

In article ,
"Kok Yong Lee" wrote:

Hi there.

I have a table of the following style.

right now i tried to find some worksheet function that will take in a
number
for "my id" or "your id" and then returned the corresponding number in
"your
id" or "my id".

for a given "my id", it is relatively simple to retrieve the
corresponding
"your id" via VLOOKUP("MY ID", "ARRAY RANGE"), as "my id" is only a
single
number and sorted ascendingly.

I am struggling in geting the reverse to work. How can I retrieve the
corresponding "my id" is I were given a "your id" number?

my id your id
------------------------------------------
52177 9561
62532.01 8910
726589 90026, 8926


mnay thanks.

My source of information has been these newsgroups and other forums.
One can learn quite a bit by watching others and asking questions.

Note that using...

SEARCH(" "&D2&","," "&B2:B4&",")

....instead of...

SEARCH(D2,B2:B4)

....ensures that SEARCH will find/match the correct value. For example,
if the value you're looking for is 8926, and A2:B4 contains the
following...

52177 9561
62532.01 89265
726589 90026, 8926

....the first one would match B4, whereas the second one would
incorrectly match B3.

Hope this helps!

In article ,
"Kok Yong Lee" wrote:

Hi Domenic,

Any where I can find more information regarding your rather exotic usage of
'&' character in SEARCH(" "&D2&","," "&B2:B4&",")?

many thanks.