IF(AND...?

[SewStage]

I feel like I'm missing the obvious here. On this tab below, column A lists kit names (KITNAME) and column B lists its associated kit category (KITCAT):

On the following tab, I want the cells in BD41-BJ45 to return a value of -1 if the entry in column B is listed in KITNAME above and the associated KITCAT matches BD1-BJ1:

Right now in cell BE50, my formula is: =IF(AND(B50=KITNAME,KITCAT=BE1),-1,) but it keeps returning a value of 0 (false) even though B50 is listed in KITNAME and the associated category does in fact match BD1.

If someone can point me to what I'm obviously doing wrong I'd appreciate it. Thanks so much. Ruth

 

[aRandomHelper]

Seems like you can use VLOOKUP:

=IF(IFERROR(VLOOKUP(B50,Sheet2!A:B,2,0),0)=BE1,-1,0)

Change 'Sheet2' to the worksheet name of your first tab.

 

[SewStage]

Seems like you can use VLOOKUP:

=IF(IFERROR(VLOOKUP(B50,Sheet2!A:B,2,0),0)=BE1,-1,0)

Change 'Sheet2' to the worksheet name of your first tab.

That worked! Thanks so much @aRandomHelper!

 

[aRandomHelper]

Glad it worked.

An improvement to lock the absolute column/row respectively, so that you can simply copy/paste the formula across the grid

=IF(IFERROR(VLOOKUP($B50,Sheet2!$A:$B,2,0),0)=BE$1,-1,0)

 

[Peter_SSs]

That worked! Thanks so much @aRandomHelper!

The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[SewStage]

The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

I thought I did, so my apologies if I marked the wrong one.

 

[Peter_SSs]

I thought I did, so my apologies if I marked the wrong one.

Yes, you had marked post #3. No problem though, it's all good now.