I have a list of 50 names in column A and a list of corresponding numbers in
Column B. If I insert one of the numbers in Col B into a cell in Col D, how
can I get the corresponding name to appear in adjacent cell in column E?
--
Sweeny

Hi

The best solution will be rearranging columns - numbers in column A, and
names in column B. Then you can use VLOOKUP - like this:
E1=VLOOKUP(D1,A$1:B$50,2,0)

With your current setup, you have to combine INDEX and MATCH functions,
something like
E1=INDEX(A$1:A$50,MATCH(D1,B$1:B$50,0),1)


Arvi Laanemets



"Sweeny" wrote in message
...
I have a list of 50 names in column A and a list of corresponding numbers
in
Column B. If I insert one of the numbers in Col B into a cell in Col D,
how
can I get the corresponding name to appear in adjacent cell in column E?
--
Sweeny

Thank you Arvi - excellent and very prompt reply will let me sleep tonight

--
Sweeny


"Arvi Laanemets" wrote:

Hi

The best solution will be rearranging columns - numbers in column A, and
names in column B. Then you can use VLOOKUP - like this:
E1=VLOOKUP(D1,A$1:B$50,2,0)

With your current setup, you have to combine INDEX and MATCH functions,
something like
E1=INDEX(A$1:A$50,MATCH(D1,B$1:B$50,0),1)


Arvi Laanemets



"Sweeny" wrote in message
...
I have a list of 50 names in column A and a list of corresponding numbers
in
Column B. If I insert one of the numbers in Col B into a cell in Col D,
how
can I get the corresponding name to appear in adjacent cell in column E?
--
Sweeny

Any chance you would reconsider the set up? It would be much easier if
you had the numbers in col A and the names in col B. Also, be sure to
have the list sorted ascending on col A.

Then you can use this simple formula:

=VLOOKUP(D1,A1:B50,2,FALSE)


Sweeny Wrote:
I have a list of 50 names in column A and a list of corresponding
numbers in
Column B. If I insert one of the numbers in Col B into a cell in Col D,
how
can I get the corresponding name to appear in adjacent cell in column
E?
--
Sweeny


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=476642

Try this:

=INDEX(A1:A50,MATCH(D1,B1:B50,0))

This assumes:
your names data is in range A1:A50,
your numbers data is in range B1:B50,
your number to lookup is in D1


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=476642

Lilliabeth wrote:
Any chance you would reconsider the set up? It would be much easier if
you had the numbers in col A and the names in col B. Also, be sure to
have the list sorted ascending on col A.

Then you can use this simple formula:

=VLOOKUP(D1,A1:B50,2,FALSE)


If *you require sorting* the data in ascending order on the numbers, it
is better to invoke:

=VLOOKUP(D1,$A$1:$B$50,2,TRUE)

or just with no switching of columns...

=LOOKUP(D1,NumberRange,NameRange)

Aladin Akyurek Wrote:

If *you require sorting* the data in ascending order on the numbers,
it
is better to invoke:

=VLOOKUP(D1,$A$1:$B$50,2,TRUE)



I think that depends on what the number refers to. Using "true" will
return a name even if the user enters an invalid number in D1. Using
the Lookup function will do the same. Not sure I like that. We really
don't have enough info to know if that is OK or not, but I would guess
more likely not.

I notice Arvi's formula used the same argument as mine did - the 0
equates to false.


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=476642

=INDEX(A:A,MATCH(D2,B:B,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sweeny" wrote in message
...
I have a list of 50 names in column A and a list of corresponding numbers
in
Column B. If I insert one of the numbers in Col B into a cell in Col D,
how
can I get the corresponding name to appear in adjacent cell in column E?
--
Sweeny

No need to sort if you use FALSE or 0

--
Regards,

Peo Sjoblom

(No private emails please)


"Lilliabeth" wrote
in message ...

Aladin Akyurek Wrote:

If *you require sorting* the data in ascending order on the numbers,
it
is better to invoke:

=VLOOKUP(D1,$A$1:$B$50,2,TRUE)



I think that depends on what the number refers to. Using "true" will
return a name even if the user enters an invalid number in D1. Using
the Lookup function will do the same. Not sure I like that. We really
don't have enough info to know if that is OK or not, but I would guess
more likely not.

I notice Arvi's formula used the same argument as mine did - the 0
equates to false.


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile:
http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=476642