can I use lokkup

[colinharwood]

Hi
The problem I have is:
In Col A are numbers 1:20
In Col B are 20 Names

Can I use a formula which will allow to enter a number between 1:20 in cell D1, which will look up that number Col A and put the corresponding name in Col B into cell E1

Thanks a lot

Colin

 

[Aladin Akyurek]

On 2002-08-25 08:00, colinharwood wrote:
Hi
The problem I have is:
In Col A are numbers 1:20
In Col B are 20 Names

Can I use a formula which will allow to enter a number between 1:20 in cell D1, which will look up that number Col A and put the corresponding name in Col B into cell E1

Thanks a lot

Colin

In E1 enter one of:

=VLOOKUP(D1,A1:B20,2,0)

=INDEX(B1:B20,MATCH(D1,A1:A20,0))

Consult also the Help file on these functions.

Note. 0 means the same thing to Excel as FALSE (and 1 --> TRUE).

Addendum. If A1:A20 is sorted in ascending order and there is no gap in sequence 1 to 20, you can omit the 0 (FALSE) arg.
This message was edited by aladin akyurek on 2002-08-25 08:37

 

[JayMenon]

Hi !

[1] Enter =VLOOKUP(D1,A1:B21),2,FALSE) in E1
[2] Please ensure the Col A is sorted in Ascendiing order.

Thanks

 

[colinharwood]

Thanks everyone

Colin

 

[colinharwood]

Is it possible to modify this so that if D1 is empty E1 is blank instead of displaying #N/A

Thanks

Colin

 

[JayMenon]

Hi !!

Try this :-

=IF(D1=0,"",VLOOKUP(D1,A1:B21,2,FALSE))

Thanks

 

[Aladin Akyurek]

On 2002-08-25 09:12, colinharwood wrote:
Is it possible to modify this so that if D1 is empty E1 is blank instead of displaying #N/A

Thanks

Colin

=IF(D1,VLOOKUP(D1,A1:B20,2,0),"")

=IF(D1,VLOOKUP(D1,A1:B20,2),"")

 

[colinharwood]

Thanks everyone

Colin