On Apr 24, 8:40*am, "Bob Phillips" wrote:
Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant)
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range

* * Set cell1 = Rows(1).Find(Co)
* * Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog)
* * Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty)
* * FindValue = Cells(Application.Match(Item, Columns(1), 0),
cell3.Column).Value

End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jlclyde" wrote in message

...



I am trying to create a UDF that will index through multiple rows of
headings. *The top heading in Row 1 is companies. *Company 1 is in
merged cells B1:G1. *Co. 2 is in merged cells H1:M1. *Under this row
of headings is a sub heading of programs that are offered by each
company. They are in B2:C2, D2:E2, F2:G2 for co. 1. *They are in
H2:I2, J2:K2, L2:M2 for co. 2. *Under this row of sub headings is
another sub heading of quantities of either 10 or 20 for each
program. *These are in cells B3 as 10 C3 as 20 for program 1. *D3 as
10 E3 as 20 for progrmam 2.... So on. *I have item numbers down the
left hand side starting at A4. *What is being indexed is the price of
the product.

Is there a way to create a UDF that will let you filter down to the
correct row and column and index? *For instance if I had Item # 3 that
would be row 6. *If I had company 1 that woudl be cells B1:G1. *I also
needed program 2 which is D2:E2, and a quantity of 20 which is in
column E. *How to get it to be the answer of the value of E6? *I am
not set on UDF, a form or other option to go about accomplishing this
task woudl *be greatly appreciated.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Bob,