# Solved Match cells and return value from another cell

Microsoft Excel 2010 - complete product...
April 8, 2015 at 11:06:42
Specs: Windows 10
 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

See More: Match cells and return value from another cell

#1
April 8, 2015 at 11:59:55
 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 1000 ```With your target data on Sheet 1 in cell B1on 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.MIKEmessage edited by mmcconaghy

Report •

#2
April 8, 2015 at 12:20:03
 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-0302```Sheet 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?ThanksSandimessage edited by SandiS

Report •

#3
April 8, 2015 at 12:50:33
 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.

Report •

Related Solutions

#4
April 8, 2015 at 13:00:38
 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

Report •

#5
April 8, 2015 at 13:04:34
 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.message edited by DerbyDad03

Report •

#6
April 8, 2015 at 13:07:09
 Agreed and thank you again for your help!Sandi

Report •

#7
April 8, 2015 at 13:07:33
 Sorry I meant thank you BOTH for your help!Sandi

Report •