VLookup with IF statement

Elizabeth Aird

I am going to try the out of spec condition using VLookup. I am not sure this is possible. But here we go.

This log has entries that will VARY but do have upper and lower limit specs. However, in the same stage one substrate may have a different u/l spec sujch as seen here with CRS UPPER limit of 175 and all others have 160.

The formula would be in the yellow column and state in " Red or nothing" based on the Vlookup from the next example. Table 1 shows entries from operators - temperature will vary in the "bath" if you will.

Example. IF CRS <120 or CRS greater than 175 - state RED

Example: IF HDG<120 and HDQ>160 - state RED

Conditional formatting based on RED will highlight the Stage 1 Temp column.

I am not good with VLookup - am trying to see what it can do for me.

Thanks!!

Paul Newcome

Remove the row number from your cell references.

[Column Name]@row

instead of

[Column Name]1@row

When using an @row reference, you are telling the formula to look in the column specified on whatever row the formula is on. You don't have to use a row number with @row. This is easier on the back-end of things and helps avoid an accidental typo that could cause the formula to pull skewed data based on an incorrect row number.

Elizabeth Aird

Paul -

If I you were here I would hug you! (PS working from home is making me a little crazy! lol)

I get it - I hope. It doesn't mean I won't have another similar dumb question down the road.

thank you!!!!

Paul Newcome

Haha. No worries. We're all a little crazy here.

And there are no "dumb" questions aside from the ones that aren't asked. How are you supposed to learn if you don't search out an answer?

Please don't forget to mark the most appropriate response(s) as "helpful". This will let others searching for a similar solution know that one may be found here.

Paul Newcome

To pull the data, you can use something like this...

=INDEX({Other Sheet Low Spec Column}, MATCH(Substrate@row, {Other Sheet Substrate Column}, 0))

=INDEX({Other Sheet High Spec Column}, MATCH(Substrate@row, {Other Sheet Substrate Column}, 0))

You can nest those in an OR statement inside of an IF:

=IF(OR([Stage 1: Temp...]@row < INDEX({Other Sheet Low Spec Column}, MATCH(Substrate@row, {Other Sheet Substrate Column}, 0)), [Stage 1: Temp...]@row > INDEX({Other Sheet High Spec Column}, MATCH(Substrate@row, {Other Sheet Substrate Column}, 0))), "Red")

Elizabeth Aird

My head just spun but I will try this! Thank you

Paul Newcome

Happy to help! 👍️

Please let me know whether or not this works for you.

Elizabeth Aird

Paul -

I still can't get this to work .. /sigh

Formula.JPG

Paul Newcome

Remove the row number from your cell references.

[Column Name]@row

instead of

[Column Name]1@row

When using an @row reference, you are telling the formula to look in the column specified on whatever row the formula is on. You don't have to use a row number with @row. This is easier on the back-end of things and helps avoid an accidental typo that could cause the formula to pull skewed data based on an incorrect row number.

Elizabeth Aird

Paul -

If I you were here I would hug you! (PS working from home is making me a little crazy! lol)

I get it - I hope. It doesn't mean I won't have another similar dumb question down the road.

thank you!!!!

Paul Newcome

Haha. No worries. We're all a little crazy here.

And there are no "dumb" questions aside from the ones that aren't asked. How are you supposed to learn if you don't search out an answer?

Please don't forget to mark the most appropriate response(s) as "helpful". This will let others searching for a similar solution know that one may be found here.