Click here for important information about Computing.net.

Microsoft Excel 2010 - complete product...

Hi, I would like to match a number from sheet 2 to the number in Column B of Sheet 1 and return the value from the cell 1 down and 1 over. The length of the sheet is always changing but the data will be consistent so that if the number is in column b the cell I need will always be 1 down and 1 over. Is this possible?

Thank you,

Sandi

You did not say what column on Sheet 2 your looking at, so I put the data for

Sheet 2 in Columns B & CSo Sheet 2 looks like:

B C 1) 10 100 2) 11 200 3) 12 300 4) 13 400 5) 14 500 6) 15 600 7) 16 700 8) 17 800 9) 18 900 10) 19 1000With your target data on Sheet 1 in cell B1

on Sheet 1 cell C1 you would enter the formula:=INDEX(Sheet2!$C$1:$C$10,MATCH(B1,Sheet2!$B$1:$B$10,0)+1)

See how that works for you.MIKE

message edited by mmcconaghy

I tried the above and am confused. Here is some sample data, to be more clear. Sheet 2 - I want to return the value of Sheet 1 cell c2 in Sheet 2 cell b1

A B 1) 15-0000-0301 2) 15-0000-0302Sheet 1

A B C 1) 1-000 15-0000-0301 2) 5/31/15 3) 4) 5) 2-000 15-0000-0302 6) 4/30/15

The number of rows between base lines 1 to 5 will always be changing so it is not consistent. It could go from 1 to 3 or 1 to 20?I am trying to take from a "form" layout and put it into a table format so it can be uploaded into our Accounting Software.

Hope this helps?

Thanks

Sandi

message edited by SandiS

A slight modification of Mike's solution works for me based on your example data: =INDEX(Sheet1!$C$1:$C$10,MATCH(A1,Sheet1!$B$1:$B$10,0)+1)

You can use a named ranged instead of cell references if that makes it easier:

=INDEX(ColumnC_Data,MATCH(A1,ColumnB_Data,0)+1)

However, I have one concern:

The number of rows between base lines 1 to 5 will always be changing so it is not consistent. It could go from 1 to 3 or 1 to 20?That shouldn't be a problem as long as the date remains 1 row below the 15-0000-xxx value. If the number of rows between those 2 values varies, then it might be hard to find a formula based solution. We may need VBA if that is the case.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

That is PERFECT!!! Thank you so much, the lines between the value and the date will not change so we should be good!! Thanks again!

Sandi

I reset the Best Answer to give Mike the credit. He came up with the answer without knowing your exact data layout. I simply changed the ranges that the formula referenced to match your data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Agreed and thank you again for your help! Sandi

Sorry I meant thank you BOTH for your help! Sandi

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History