Help .. Lookup and match UDF/macro
 

Hi all:

Basically, the aim of the exercise is to write a macro or UDF which will
execute and paste the values in the corresponding cells.
'sheet1' is a master database with 19 fields.
The user enters the key value in 'sheet2'. Then our macro/UDF is executed.

Outline of the problem:

1. User enters the key
2. do vlookups (on sheet1) to paste the corresponding Pr_Wk,Pr_Sl,Pr_Rt data.
3. User then enters max -ve and max +ve range values (to be applied on Pr_Rt
data) in sheet2.
4. do lookups to find the first five closest values in the neighborhood of
the max -ve (Pr_Rt) and max +ve (Pr_Rt) ranges, return the key values in key
1,key2,key3,key4,key5 fields of 'sheet2'
5. also paste the pre 4 wk% and post 4 wk% adjacent to the corresponding key
values. (pre 4 wk% and post 4 wk% are the last two columns of sheet1)

Your help is greatly appreciated!

Thanks and Regards<
Murthy

sheet1 sheet2
Key Key
Pr_Wk Pr_Wk
Pr_Sl Pr_Sl
Pr_Rt Pr_Rt
Po_Wk Max -ve
Po_Sl Max +ve
Post_1 Key 1
Post_2 Key 1 Pre 4 Wk %
Post_3 Key 1 Post 4 Wk %
Post_4 Key 2
Post_5 Key 2 Pre 4 Wk %
Pre_1 Key 2 Post 4 Wk %
Pre_2 Key 3
Pre_3 Key 3 Pre 4 Wk %
Pre_4 Key 3 Post 4 Wk %
Pre_5 Key 4
Pre-4-Wk-% Key 4 Pre 4 Wk %
Post-4-Wk-% Key 4 Post 4 Wk %
Key 5
Key 5 Pre 4 Wk %
Key 5 Post 4 Wk %