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)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Number
[/TD]
[TD]Description
[/TD]
[TD]other
[/TD]
[/TR]
[TR]
[TD]77-dept
[/TD]
[TD]11
[/TD]
[TD].......
[/TD]
[TD].......
[/TD]
[/TR]
[TR]
[TD]77-dept
[/TD]
[TD]12
[/TD]
[TD].......
[/TD]
[TD].......
[/TD]
[/TR]
[TR]
[TD]77-dept
[/TD]
[TD]13
[/TD]
[TD].......
[/TD]
[TD].......
[/TD]
[/TR]
[TR]
[TD]88-name
[/TD]
[TD]14
[/TD]
[TD].......
[/TD]
[TD].......
[/TD]
[/TR]
</tbody>[/TABLE]

Second tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Number
[/TD]
[TD]Descitp...
[/TD]
[/TR]
[TR]
[TD]77-Dept
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11
[/TD]
[TD].......
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12
[/TD]
[TD].......
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13
[/TD]
[TD].......[/TD]
[/TR]
[TR]
[TD]88-name
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14
[/TD]
[TD].......
[/TD]
[/TR]
</tbody>[/TABLE]


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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Number​

[/td][td]

Description​

[/td][td]

other​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

77-Dept​

[/td][td]

11​

[/td][td]

.......​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

77-Dept​

[/td][td]

12​

[/td][td]

.......​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

77-Dept​

[/td][td]

13​

[/td][td]

.......​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

88-name​

[/td][td]

14​

[/td][td]

.......​

[/td][td]

.......​

[/td][/tr]
[/table]

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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Number​

[/td][td]

Descitp...​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

77-Dept​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

11​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

12​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

13​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

88-name​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

14​

[/td][td]

.......​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.