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

Report •

#1
April 8, 2015 at 11:59:55
✔ Best Answer
You did not say what column on Sheet 2 your looking at, so I put the data for
Sheet 2 in Columns B & C

So 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 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

http://www.skeptic.com/

message 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?

Thanks
Sandi

message 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.

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


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.

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

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 •

Ask Question