Compare values in 2 ranges
 

In a spreadsheet from cell D4 to D23 i have customer numbers, they are ranked
1 to 20 for sales in 2005. the rankings 1 to 20 are in column C range c4 to
c23 In cell I4 toI23 same thing except it is 2004 sales. They are ranked 1
to 20 which are in column H range H4 to h23

What I am trying to to is compare the customer number in column D and look
in Column I, when it finds a match then I want to look in the rankings column
H and put that value in Column B next to the same customer number.

The end result is I would be able to see where they were in 2004 against 2005

Compare values in 2 ranges
 

Hi Nigel,

If I read your post correctly the formula below will work in Column C.

=INDEX($H$4:$H$23,MATCH(D4,$I$4:$I$23,0),1)

Enter this formula into Cell C4 and then copy it down to Cell C23. The
formula is setup with the following in mind:

Column D (D4:D23) = Customer Numbers
Column E (E4:E23) = 2005 Ranking
Column H (H4:H23) = 2004 Ranking
Column I (I4:I23) = Customer Numbers
Column C - the column that performs lookup for 2004 rank of Column D
Customer Number.

Hope that helps.

Regards,
James
"Nigel" wrote:

In a spreadsheet from cell D4 to D23 i have customer numbers, they are ranked
1 to 20 for sales in 2005. the rankings 1 to 20 are in column C range c4 to
c23 In cell I4 toI23 same thing except it is 2004 sales. They are ranked 1
to 20 which are in column H range H4 to h23

What I am trying to to is compare the customer number in column D and look
in Column I, when it finds a match then I want to look in the rankings column
H and put that value in Column B next to the same customer number.

The end result is I would be able to see where they were in 2004 against 2005

Compare values in 2 ranges
 

Absolutely PERFECT

Thanks

"JS2004R6" wrote:

Hi Nigel,

If I read your post correctly the formula below will work in Column C.

=INDEX($H$4:$H$23,MATCH(D4,$I$4:$I$23,0),1)

Enter this formula into Cell C4 and then copy it down to Cell C23. The
formula is setup with the following in mind:

Column D (D4:D23) = Customer Numbers
Column E (E4:E23) = 2005 Ranking
Column H (H4:H23) = 2004 Ranking
Column I (I4:I23) = Customer Numbers
Column C - the column that performs lookup for 2004 rank of Column D
Customer Number.

Hope that helps.

Regards,
James
"Nigel" wrote:

In a spreadsheet from cell D4 to D23 i have customer numbers, they are ranked
1 to 20 for sales in 2005. the rankings 1 to 20 are in column C range c4 to
c23 In cell I4 toI23 same thing except it is 2004 sales. They are ranked 1
to 20 which are in column H range H4 to h23

What I am trying to to is compare the customer number in column D and look
in Column I, when it finds a match then I want to look in the rankings column
H and put that value in Column B next to the same customer number.

The end result is I would be able to see where they were in 2004 against 2005