need help with a complicated lookup formula

[residnt]

Hello There,

Here is what I'm trying to do. I have 2 sheets where on sheet 2 I need a list derived from contents in sheet one based on criteria. I cannot use the vlookup as the information isn't laid out in a format that will work, left to right not right to left. I am going to try and create a UDF for this but I have not been successful referencing ranges on different sheets.

This is what I need to happen

on sheet 2 the formula will need to have 2 different criterias, 1 being department and the other being item number, if those match then return the value of the cell in column A for the matched references

Is there an easy way of doing this. Since these contents change all of the time the list on sheet 2 will always be up todate with the latest information.

Thanks
residnt

 

[residnt]

Ok then how can I get a UDF to work on a range specified in a different sheet. I have the selected range in my formula =myfun(sheet1a5:a35) however when I use this formula on sheet 2 I only get the range address of a5:a35 without the sheet name.

Thanks
residnt

 

[DonkeyOte]

what are the values in column A ?

you could use a Sumproduct by the sound of it

=SUMPRODUCT(--(Sheet1!B1:B1000=criteria1),--(Sheet1!C1:C1000=criteria2),--(sheet1!A1:A1000))

we don't know your ranges so this assumes criteria 1 is found in column B on first sheet, criteira 2 in column C and the results are in A -- also assumes results are numeric.

if not let us know...and provide ranges etc and contents in terms of data types.

 

[residnt]

the sum product itself wouldn't work as I'm not looking at summing anything together. I'm looking to put together a dymanic list based on criteria.

So the list will be locted on sheet 2, with all of the criteria listed on sheet 1

criteria 1 = column J
criteria 2 = column I

Output will need to be column a & b & h

I know I could use an AND function but I want a dynamic list, so if it doesn't match go to the next one.

I know know if i made that any clearer?

residnt

 

[residnt]

Do you know how to reference a specified range in a UDF?

 

[DonkeyOte]

can you use the list on sheet1 as the source for a pivot table?

-- make the fields that contain property 1 and property 2 "page" fields
-- the return value could be a row field
-- make "data" field a count of another field

by changing the page fields accordingly you will get your revised listing.

if you set the pivot range with some spare capacity it will handle changing data.

if not please post up some samples of the data you're working with along with ranges containing criteria etc... and expected results... and if you want post your current UDF up also.

 

[Padawan]

residnt

Could you use a lookup table if you changed the Excel default from left to right to right to left?

This is changed at Tools->Options->International.

Regards,

 

[DonkeyOte]

not if there are going to be multiple matches - if it were a single match you could use INDEX/MATCH to lookup in any order (no need to alter XL defaults).

 

[Padawan]

Fair enough.

Thanks Lasw10

 

[Guanjin Peter]

just an additonal note, sumProduct formula will will always return a 0, if the returned array is alpha-numberic(abc123) or alphabetical (abc)

if sumproduct doesn't work for you, you can index(sumproduct, rows(returned value))