Displaying a number from another column

[Natalie]

Hi,

Need some help:

Let's say I have a list of numbers in column A:

A1:1
A2:2
A3:3
An:n

And another list of random numbers in column B:

B1: 2.3
B2: 3.5
B3: 55.6
Bn: <some random number>

If I enter a number in cell C1=An, how do I make cell D1 display the
corresponding B column value?

Eg: If in C1, I enter "2", how do I make D1 show "55.6"

Thanks!

 

[Pete_UK]

Enter this in D1:

=VLOOKUP(C1,A1:Bn,2,0)

Change Bn to suit your range. If you have no value in C1 or if the
value is not part of your list, you will get the error message #N/A.

Hope this helps.

Pete

 

[Dave Peterson]

=vlookup(d1,a:b,2,false)

Hi,

Need some help:

Let's say I have a list of numbers in column A:

A1:1
A2:2
A3:3
An:n

And another list of random numbers in column B:

B1: 2.3
B2: 3.5
B3: 55.6
Bn: <some random number>

If I enter a number in cell C1=An, how do I make cell D1 display the
corresponding B column value?

Eg: If in C1, I enter "2", how do I make D1 show "55.6"

Thanks!

 

[Dave Peterson]

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())

 

[Mark]

Hi,

Need some help:

Let's say I have a list of numbers in column A:

A1:1
A2:2
A3:3
An:n

And another list of random numbers in column B:

B1: 2.3
B2: 3.5
B3: 55.6
Bn: <some random number>

If I enter a number in cell C1=An, how do I make cell D1 display the
corresponding B column value?

Eg: If in C1, I enter "2", how do I make D1 show "55.6"

Thanks!

Use a formula similar to this in cell D1: =VLOOKUP(C1,A1:Bn,2)

 

[Natalie]

THANKS ALL FOR THE HELP!!

I have another related question about Vlookup.

I'm trying to lookup a number that has a really long decimal figure:
eg: 2.33334444555666777

Is there anyway I can search for it by just typing "2.3333" instead of
the whole long number? Thanks again!

Nat

 

[Dave Peterson]

It depends on what you want to accomplish.

If your table in A1:Bxx is sorted nicely (by column A), you may be able to use:

=VLOOKUP(D1,A:B,2)
which is equivalent to:
=VLOOKUP(D1,A:B,2,TRUE)
(TRUE is the default for that 4th parameter.

But read the help for =vlookup() and experiment a little to see how it works.

This describes the 4th parm from xl2003's help:

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an approximate
match is returned. In other words, if an exact match is not found, the next
largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will
find an exact match. If one is not found, the error value #N/A is returned.

 

[Natalie]

Dave, this is awesome. Thanks again =)

It depends on what you want to accomplish.

If your table in A1:Bxx is sorted nicely (by column A), you may be able to use:

=VLOOKUP(D1,A:B,2)
which is equivalent to:
=VLOOKUP(D1,A:B,2,TRUE)
(TRUE is the default for that 4th parameter.

But read the help for =vlookup() and experiment a little to see how it works.

This describes the 4th parm from xl2003's help:

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an approximate
match is returned. In other words, if an exact match is not found, the next
largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will
find an exact match. If one is not found, the error value #N/A is returned.