# need help with a complicated lookup formula

#### residnt

##### Board Regular
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### residnt

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

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

#### DonkeyOte

##### MrExcel MVP
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.

##### Active Member
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

##### MrExcel MVP
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).

Fair enough.

Thanks Lasw10

#### Guanjin Peter

##### Active Member
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))

Replies
0
Views
250
Replies
2
Views
28
Replies
5
Views
332
Replies
0
Views
120
Replies
1
Views
63

1,191,619
Messages
5,987,723
Members
440,106
Latest member
davcurnutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back