Lookup Question Based upon 2 Variables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lois - 23 May 2008 23:07 GMT

I have read the other posts similar to my question - but I still do not
understand. Please help.

My problem is I would like to lookup a code that is specific to a value to
determine if it exists in a table of data I have created. For example the
following table is named "DATA".

VALUE CODE
1111 505
1111 506
1111 507
1112 505
1112 609
1112 803

I have a value in cell F3 and a code in cell I3. I want to be able to
create a formula in cell M3 which will determine if I have a valid code for
the value. For instance I need to determine if code 505 is valid for Value
1111. Since it is I need a response of "Valid". On the other hand if I have
a code of 803 for value 1111 I need a response of "Invalid".

Thanks for your help!!

Reply to this Message

Shane Devenshire - 23 May 2008 23:28 GMT

Hi Lois,

Try this formula:

=IF(OR((A2:A10=I3)*(B2:B10=F3)),"Valid","Invalid")

Enter this as an array formula by pressing Shift+Ctrl+Enter instead of
Enter. Add absolute references if you intend to copy it down.
=IF(OR((A$1:A$4=I3)*(B$1:B$4=F3)),"Valid","Invalid")
I put the table you display in A1:B10.

Cheers,
Shane Devenshire
Microsoft Excel MVP

> I have read the other posts similar to my question - but I still do not
> understand. Please help.
[quoted text clipped - 21 lines]
>
> Thanks for your help!!

Reply to this Message

Peo Sjoblom - 23 May 2008 23:37 GMT

One possible way

=IF(SUMPRODUCT(--(A1:A10=I3),--(B1:B10=F3))>0,"Valid","Invalid")

replace A1:A10 and B1:B10 with the 2 value and code ranges

Signature

Regards,

Peo Sjoblom

>I have read the other posts similar to my question - but I still do not
> understand. Please help.
[quoted text clipped - 21 lines]
>
> Thanks for your help!!

Reply to this Message