What kind of lookup function would work for this?

[anewc1]

Excel Workbook
	A
1	Employee No: 56-0003051 Ovalle, Esmeralda
2	-------------------------------------------------------------------------------
3	Address: 558 Comal St. Sort Field: EGP Offic
4
5	City: Eagle Pass Marital Status: M
6	State: TX ZIP CODE: 78852 Sex: F
7	Statutory Employee: N
8	Phone No: ( ) - Race Code: H
9	Soc Sec No: xxx-63-7479 Pens/Prof Shr: N
10	Birth Date: 05/31/70 Direct Dep: N
11	Hire Date: 03/13/01 COMMENT:
12	Term/Inact: COMMENT:
13	Review Date: COMMENT:
Sheet1

I have a text document that is way too long to print out, and it would be easier if I could put together the information in a spreadsheet form. When I open the document in Excel, it is all one column straight down. I want to be able to look up a social security number, then return the information in a specific row above or below it. For example, if I look up the social in the example, I want to also return the name of the employee that is a set 8 rows above it, the address that is 6 rows above the social, the city and state that are 4 rows above the social, and so on. What kind of lookup function would work for this? TIA!

 

[Aladin Akyurek]

X2:

=LOOKUP(9.99999999999999E+307,SEARCH(LookupValue,Range))

Once you have the position ("row"), you can retrieve the needed info with:

INDEX(Range,SUM(X2,N))

 

[anewc1]

I'm sorry, that's a bit too vague, I don't understand. Why is the first value in the Lookup function 9.99999999999999E+307? "LookupValue" in the function would be a the SS#, and Range would be $A$1:$A$8831?

For the Index function, which range does Range refer to? When you say to put that formula in X2, is that just a random cell you picked? What does "N" refer to?

Thanks for any help.

 

[Aladin Akyurek]

I'm sorry, that's a bit too vague, I don't understand. Why is the first value in the Lookup function 9.99999999999999E+307? "LookupValue" in the function would be a the SS#, and Range would be $A$1:$A$8831?

For the Index function, which range does Range refer to? When you say to put that formula in X2, is that just a random cell you picked? What does "N" refer to?

Thanks for any help.

To recap, also because my proposal wasn't complete...

I1: xxx-63-7479

I2:

=LOOKUP(9.99999999999999E+307,
   SEARCH(I1,$A$1:$A$8831),
   ROW($A$1:$A$8831)-ROW($A$1)+1)

I3:

=INDEX($A$1:$A$8831,I2-4)

I1 is SS# of interest.
I2 determines the position of I1, relative to $A$1:$A$8831.
I3 returns the text at I2-4.

 

[anewc1]

AWESOME. Thank you so much for clarifying, you just saved me a lot of time! Have a great day!

 

[Aladin Akyurek]

AWESOME. Thank you so much for clarifying, you just saved me a lot of time! Have a great day!

You are welcome & thanks for the feedback.