# LookUP <> not working

1. ## LookUP <> not working

Hi,

The <> part of the following does not seem to be working. Any ideas why?

=LOOKUP(2,1/((Sheet1!\$E\$2:\$E\$1481=Sheet2!\$A2)*(Sheet1!\$P\$2:\$P\$1599="GPPS")*(Sheet1!\$P\$2:\$P\$1599<>Sheet2!\$B2)*(Sheet1!\$O\$2:\$O\$1599=0)),Sheet1!\$A\$2:\$A\$1599)

Thank you

2. ## Re: LookUP <> not working

I don't understand the logic of:

Sheet2!P2:P1599="GPPS"
Sheet2!P2:P1599<>Sheet2!B2

are you referencing correctly ?

3. ## Re: LookUP <> not working

Firstly, you have different range sizes... range in Column E differs.. fix that and then come back if still having issues...

4. ## Re: LookUP <> not working

Thank you for your feedback. Yes, you're right, I wasn't referencing correctly. The correct formula is below which now works fine.

The reason I have the <> part in the formula is because there can be up to 5 values that meet criteria so I run the formula once without the <> part and then a further 4 times, each time adding an additional <> part. Is it possible to write the formula so that all values that meet criteria can be returned in the same field, separated by a comma?

Thank you

Thanks

=LOOKUP(2,1/((Sheet1!\$E\$2:\$E\$1481=Sheet2!\$A2)*(Sheet1!\$P\$2:\$P\$1599="GPPS")*(Sheet1!\$A\$2:\$A\$1599<>Sheet2!\$B2)*(Sheet1!\$A\$2:\$A\$1599<>Sheet2!\$C2)*(Sheet1!\$O\$2:\$O\$1599=0)),Sheet1!\$A\$2:\$A\$1599)

5. ## Re: LookUP <> not working

What about the mismatch in array sizes?

6. ## Re: LookUP <> not working

Hi,

Thanks again for your response. Mismatch corrected. Any ideas re may latest question?

Thanks

=LOOKUP(2,1/((Sheet1!\$E\$2:\$E\$1599=Sheet2!\$A2)*(Sheet1!\$P\$2:\$P\$1599="GPPS")*(Sheet1!\$A\$2:\$A\$1599<>Sheet2!\$B2)*(Sheet1!\$A\$2:\$A\$1599<>Sheet2!\$C2)*(Sheet1!\$O\$2:\$O\$1599=0)),Sheet1!\$A\$2:\$A\$1599)

7. ## Re: LookUP <> not working

Add this user defined function to a new module in the VB editor:

``Please Login or Register  to view this content.``
Then apply formula:
``Please Login or Register  to view this content.``
And confirm it with CTRL+ShIFT+ENTER

This separates each enry with a comma and space.

Note: Not sure of limitations

8. ## Re: LookUP <> not working

This works great! Thank you.

Is this possible to do without VBA? Reason for asking is if I could do this in a function then I would be able to understand it better and learn how to apply it to future problems.

Thanks

9. ## Re: LookUP <> not working

Not really, unless you want several helper columns which concatenates groups at a time, then you concat all the groups together at the end... and since Concatenate() function does not allow you to enter a range, rather, individual cell references, this would be a tedious process...

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1