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 so this is what I have done. I have created a new column in the front of all of the data that will be hidden which will have the criteria that I need to check. Now I am able to use a vlookup formula, however how do I get it to return the next occurance in the table?

thanks
residnt

 

[Guanjin Peter]

if that's the case, you need to adjust the formula accordinglty.

let's say:

Column A --- Column B
aaaaaaaa --- =Formula(a2)
aaaaaaaa --- =Formula(a3)
aaaaaaaa --- =Formula(a4)

Above may or may not work for you. This would change accordingly to your formula

 

[residnt]

Sorry for the late reply but I figured it out. I used a modified version of the multiple lookup from a different forum.

Thanks everyone for their suggestions and help

Residnt