offset and index/match

[dmj120]

I've found a things, but I can't piece this together. From tab 1, I need to find and item on tab 2, move 1 cell left, and up to the next cell with data.

First Tab: (the small blue info is what I want to pull from tab two)

Name	Number	Description	other
77-dept	11	.......	.......
77-dept	12	.......	.......
77-dept	13	.......	.......
88-name	14	.......	.......

<tbody>
</tbody>

Second tab:

Name	Number	Descitp...
77-Dept
	11	.......
	12	.......
	13	.......
88-name
	14	.......

<tbody>
</tbody>

I've got an index/indirect/match formula to work to pull specific info, which works very well. But trying to include an offset is proving to be a hurdle. Should I be looking at a lookup() formula?

 

[Marcelo Branco]

Maybe

Assuming data in columns A:D (Sheet1 and Sheet2)
Formula in A2 of Sheet1 copied down
=LOOKUP(2,1/(Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))<>""),Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)))

Hope this helps

M.

 

[dmj120]

Thanks! That works in the first cell on sheet 1, but when I copy down, the cells are "blank." Not sure why, the cell references look correct.

 

[dmj120]

Even more odd.... I copied the formula further down and populate and and some dont.

 

[Marcelo Branco]

Worked perfectly for me

Sheet1

	A​	B​	C​	D​
1​	Name​	Number​	Description​	other​
2​	77-Dept​	11​	.......​	.......​
3​	77-Dept​	12​	.......​	.......​
4​	77-Dept​	13​	.......​	.......​
5​	88-name​	14​	.......​	.......​

Formula in A2 copied down
=LOOKUP(2,1/(Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))<>""),Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)))

Sheet2

	A​	B​	C​
1​	Name​	Number​	Descitp...​
2​	77-Dept​
3​		11​	.......​
4​		12​	.......​
5​		13​	.......​
6​	88-name​
7​		14​	.......​
8​

M.

 

[dmj120]

the cell references are not the the same. I missed a "$" It's working great!! Thanks


=LOOKUP(2,1/(Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))<>""),Sheet2!A$2:INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)))

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.