Function problem

S

Sweeny

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?
 
A

Arvi Laanemets

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
 
L

Lilliabeth

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

Then you can use this simple formula:

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

Cutter

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
 
A

Aladin Akyurek

Lilliabeth said:
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)
Click to expand...

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)
 
L

Lilliabeth

Aladin said:
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)
Click to expand...

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.
 
B

Bob Phillips

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

--

HTH

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

Peo Sjoblom

No need to sort if you use FALSE or 0

--
Regards,

Peo Sjoblom

(No private emails please)
 
Top