# LOOKUP formula with 3 conditions and sort/filter function

1. ## LOOKUP formula with 3 conditions and sort/filter function

Hi Guys,
I have a formula that I use to look up a value according to 3 conditions. But when I reorder the rows using a sort & filter function, the formula locks for cells \$C11 & \$F11 are still locked to the original cell rows (but I don't wish to lock them). I want them to retrieve the value after the sort.

=IFERROR(LOOKUP(2,1/((Real!\$F11='MILL'!\$A\$1:\$A\$100000)*(Real!\$C11='MILL'!\$B\$1:\$B\$100000)*(Real!\$J\$9='MILL '!\$C\$1:\$C\$100000)),'MILL'!\$D\$1:\$D\$100000),"-")

Any help would be great.

Thanks,

2. ## Re: LOOKUP formula with 3 conditions and sort/filter function

In which worksheet is the formula?

3. ## Re: LOOKUP formula with 3 conditions and sort/filter function

The formula is on worksheet: Real

4. ## Re: LOOKUP formula with 3 conditions and sort/filter function

It should sort correctly if you take out the Real! worksheet names, i.e. like this

=IFERROR(LOOKUP(2,1/(\$F11=Mill!\$A\$1:\$A\$100000)*(\$C11=Mill!\$B\$1:\$B\$100000)*(\$J\$9=Mill!\$C\$1:\$C\$100000),Mill!\$D\$1:\$D\$100000),"-")

5. ## Re: LOOKUP formula with 3 conditions and sort/filter function

Looks to be working. Thanks legs!

There are currently 1 users browsing this thread. (0 members and 1 guests)