Prev: Setting special format for a cell
Next: slow worksheet
From: Zakynthos on 14 Mar 2010 17:13
Hi,

In col A I have numbers 1 to 500 in sequential order
In column B, I have values against each of these numbers
In column C I have numbers 1 to 500 in non-sequential order
In column D I want to match each number in column C with its equivalent in
column A and return the corresponding variable in column B to column D

For example, I have 8 in cell B25 and 25 in C1 and want to return 8 in D1
I have 15 in B107 and 107 in C2 and want to return 15 in D2 etc

Do I use LOOKUP, VLOOKUP etc for this or something else?

Can you give me an example of the formula?

Many thanks.


From: Joe User on 14 Mar 2010 17:29
"Zakynthos" wrote:
> Do I use LOOKUP, VLOOKUP etc for this or something else?

You can make either work. But in your case, the simplest might be: put the
following formula into D1 and copy down:

=vlookup(C1,$A$1:$B$500,2)

However, you might not need column A if your numbers are always sequential
starting with 1, 2 etc. In that case, you might consider:

=index($B$1:$B$500,C1)


----- original message -----

"Zakynthos" wrote:
> Hi,
>
> In col A I have numbers 1 to 500 in sequential order
> In column B, I have values against each of these numbers
> In column C I have numbers 1 to 500 in non-sequential order
> In column D I want to match each number in column C with its equivalent in
> column A and return the corresponding variable in column B to column D
>
> For example, I have 8 in cell B25 and 25 in C1 and want to return 8 in D1
> I have 15 in B107 and 107 in C2 and want to return 15 in D2 etc
>
> Do I use LOOKUP, VLOOKUP etc for this or something else?
>
> Can you give me an example of the formula?
>
> Many thanks.
>
>
From: Zakynthos on 14 Mar 2010 19:28
Thanks, very helpful, I'll give it a try!

"Joe User" wrote:

> "Zakynthos" wrote:
> > Do I use LOOKUP, VLOOKUP etc for this or something else?
>
> You can make either work. But in your case, the simplest might be: put the
> following formula into D1 and copy down:
>
> =vlookup(C1,$A$1:$B$500,2)
>
> However, you might not need column A if your numbers are always sequential
> starting with 1, 2 etc. In that case, you might consider:
>
> =index($B$1:$B$500,C1)
>
>
> ----- original message -----
>
> "Zakynthos" wrote:
> > Hi,
> >
> > In col A I have numbers 1 to 500 in sequential order
> > In column B, I have values against each of these numbers
> > In column C I have numbers 1 to 500 in non-sequential order
> > In column D I want to match each number in column C with its equivalent in
> > column A and return the corresponding variable in column B to column D
> >
> > For example, I have 8 in cell B25 and 25 in C1 and want to return 8 in D1
> > I have 15 in B107 and 107 in C2 and want to return 15 in D2 etc
> >
> > Do I use LOOKUP, VLOOKUP etc for this or something else?
> >
> > Can you give me an example of the formula?
> >
> > Many thanks.
> >
> >
 | 
Pages: 1
Prev: Setting special format for a cell
Next: slow worksheet