Help needed: Complicated lookup, Return text from the same column at a variable distance.

[r4dyce]

Hi there. I'm still pretty much a novice at excel so forgive my ignorance. I have been given two reports. One is a list of Names and transaction codes listed in a single column, along with items for that transaction. The other is a list of transaction codes for a certain time frame. Examples below. What I need to do is get the names associated with each transaction code. Essentially what I would like to do is look up the code from sheet 2 in sheet 1 and return the closest name associated with that code. Unfortunately it's a variable distance from the code to the name above so I can't simply vlookup/index to return the cell above. Working in my favor the name is always in bold text and is always the nearest text above the listed code in sheet 1. Ideally I'd like to ask excel "Find this number in sheet 1 and return the nearest text in the column above that cell" but I don't know how to translate that into an excel function or if it's even possible. Maybe I'm going about this all the wrong way? I'm totally open to suggestions and advice!

Thanks,
Ray

Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]code
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]110
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]117
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]JAMES
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110
[/TD]
[TD]Item X
[/TD]
[/TR]
[TR]
[TD]110
[/TD]
[TD]Item Y
[/TD]
[/TR]
[TR]
[TD]117
[/TD]
[TD]Item Z
[/TD]
[/TR]
[TR]
[TD]MARK
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]Item X
[/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]Item B
[/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]Item C
[/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]Item F
[/TD]
[/TR]
[TR]
[TD]115
[/TD]
[TD]Item H
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]Item Z
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]Item X
[/TD]
[/TR]
[TR]
[TD]CHRIS
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]120
[/TD]
[TD]Item C
[/TD]
[/TR]
[TR]
[TD]120
[/TD]
[TD]Item A
[/TD]
[/TR]
[TR]
[TD]125
[/TD]
[TD]Item X
[/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]Item A
[/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]Item B
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try this array formula:

=INDEX(Sheet1!A:A,MATCH(MATCH(A2,Sheet1!$A$1:$A$20,0),IF(NOT(ISNUMBER(Sheet1!$A$1:$A$20)),ROW(Sheet1!$A$1:$A$20))))

enter it into the formula bar, adjust the ranges to match your sheets, then press Control+Shift+Enter. Then you can drag it down as needed.