software

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 quot;my idquot; or quot;your idquot; and then returned the corresponding number in quot;your
idquot; or quot;my idquot;.

for a given quot;my idquot;, it is relatively simple to retrieve the corresponding
quot;your idquot; via VLOOKUP(quot;MY IDquot;, quot;ARRAY RANGEquot;), as quot;my idquot; is only a single
number and sorted ascendingly.

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

my id your id
------------------------------------------
52177 9561
62532.01 8910
726589 90026, 8926mnay 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.

PeteThanks Pete,

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

The remaining problem right now is my quot;your idquot; column might contain a fwe
number like: quot;8914, 8916quot;; MATCH just dont like it at all.quot;Pete_UKquot; gt; wrote in message ups.com...
gt; Use the MATCH function to find the row in your_id and embed this within
gt; the INDEX function to retrieve my_id. When you use VLOOKUP the lookup
gt; value must be in the left hand column of the table, so you can't use
gt; this for what you want to do.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
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(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)),0))

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

Hope this helps!

In article gt;,
quot;Kok Yong Leequot; gt; wrote:

gt; Hi there.
gt;
gt; I have a table of the following style.
gt;
gt; right now i tried to find some worksheet function that will take in a number
gt; for quot;my idquot; or quot;your idquot; and then returned the corresponding number in quot;your
gt; idquot; or quot;my idquot;.
gt;
gt; for a given quot;my idquot;, it is relatively simple to retrieve the corresponding
gt; quot;your idquot; via VLOOKUP(quot;MY IDquot;, quot;ARRAY RANGEquot;), as quot;my idquot; is only a single
gt; number and sorted ascendingly.
gt;
gt; I am struggling in geting the reverse to work. How can I retrieve the
gt; corresponding quot;my idquot; is I were given a quot;your idquot; number?
gt;
gt; my id your id
gt; ------------------------------------------
gt; 52177 9561
gt; 62532.01 8910
gt; 726589 90026, 8926
gt;
gt;
gt; mnay thanks.

Hi Domenic,

That does the trick! Excellent.

Thanks a million.quot;Domenicquot; gt; wrote in message
...
gt; Assuming that A2:B4 contains your data, try the following formula which
gt; needs to be confirmed with CONTROL SHIFT ENTER, not just ENTER...
gt;
gt; =INDEX(A2:A4,MATCH(TRUE,ISNUMBER(SEARCH(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)),0))
gt;
gt; ...where D2 contains the 'your id' of interest, such as 90026.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Kok Yong Leequot; gt; wrote:
gt;
gt;gt; Hi there.
gt;gt;
gt;gt; I have a table of the following style.
gt;gt;
gt;gt; right now i tried to find some worksheet function that will take in a
gt;gt; number
gt;gt; for quot;my idquot; or quot;your idquot; and then returned the corresponding number in
gt;gt; quot;your
gt;gt; idquot; or quot;my idquot;.
gt;gt;
gt;gt; for a given quot;my idquot;, it is relatively simple to retrieve the
gt;gt; corresponding
gt;gt; quot;your idquot; via VLOOKUP(quot;MY IDquot;, quot;ARRAY RANGEquot;), as quot;my idquot; is only a
gt;gt; single
gt;gt; number and sorted ascendingly.
gt;gt;
gt;gt; I am struggling in geting the reverse to work. How can I retrieve the
gt;gt; corresponding quot;my idquot; is I were given a quot;your idquot; number?
gt;gt;
gt;gt; my id your id
gt;gt; ------------------------------------------
gt;gt; 52177 9561
gt;gt; 62532.01 8910
gt;gt; 726589 90026, 8926
gt;gt;
gt;gt;
gt;gt; mnay thanks.
Hi Domenic,

Any where I can find more information regarding your rather exotic usage of
'amp;' character in SEARCH(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)?

many thanks.

quot;Domenicquot; gt; wrote in message
...
gt; Assuming that A2:B4 contains your data, try the following formula which
gt; needs to be confirmed with CONTROL SHIFT ENTER, not just ENTER...
gt;
gt; =INDEX(A2:A4,MATCH(TRUE,ISNUMBER(SEARCH(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)),0))
gt;
gt; ...where D2 contains the 'your id' of interest, such as 90026.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Kok Yong Leequot; gt; wrote:
gt;
gt;gt; Hi there.
gt;gt;
gt;gt; I have a table of the following style.
gt;gt;
gt;gt; right now i tried to find some worksheet function that will take in a
gt;gt; number
gt;gt; for quot;my idquot; or quot;your idquot; and then returned the corresponding number in
gt;gt; quot;your
gt;gt; idquot; or quot;my idquot;.
gt;gt;
gt;gt; for a given quot;my idquot;, it is relatively simple to retrieve the
gt;gt; corresponding
gt;gt; quot;your idquot; via VLOOKUP(quot;MY IDquot;, quot;ARRAY RANGEquot;), as quot;my idquot; is only a
gt;gt; single
gt;gt; number and sorted ascendingly.
gt;gt;
gt;gt; I am struggling in geting the reverse to work. How can I retrieve the
gt;gt; corresponding quot;my idquot; is I were given a quot;your idquot; number?
gt;gt;
gt;gt; my id your id
gt;gt; ------------------------------------------
gt;gt; 52177 9561
gt;gt; 62532.01 8910
gt;gt; 726589 90026, 8926
gt;gt;
gt;gt;
gt;gt; 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(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)

....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 gt;,
quot;Kok Yong Leequot; gt; wrote:

gt; Hi Domenic,
gt;
gt; Any where I can find more information regarding your rather exotic usage of
gt; 'amp;' character in SEARCH(quot; quot;amp;D2amp;quot;,quot;,quot; quot;amp;B2:B4amp;quot;,quot;)?
gt;
gt; many thanks.