Compare 2 work sheet cells and populate based on results

Can some one help me with simple formula to do below job for me?
I have 2 work sheets

Work sheet name : Claims. This has columns A till I

How to populate I2 value

When Cell A2 has value "Krish" or "Jacks", We need to apply below formula in I2
Take cell H2 value in this Claims work sheet, Compare with cell A1 value in another work sheet called Reference
If the values match, take C2 value in Reference work sheet and populate value in I2 of Claims work sheet
If the value does not match, populate as "Offshore" in I2 of Claims work sheet
When Cell A2 does not have value "Krish" or "Jacks", We need to mention "no match"

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

Better - thank you.

The error #NAME? from your sample Excel because you are referring to a sheets is not in your worksheet which is "ILC data!A2"

=IF(OR(ILC data!A2="Krish",ILC data!A2="Jacks"),IF(ILC data!H2=Reference!A1,Reference!C2,"Offshore"),"")


So I modify the formula as per below that would give you the result as "Offshore" or "no match"

=IF(OR(A18="Krish",A18="Jacks"),IF(H18=Reference!A17,Reference!C18,"Offshore"),"no match")

Regards,
Rev12




If helpful to you;
- Please click on the *Add Reputation button at the bottom left.

Then mark your thread as SOLVED if meets your need:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by Rev12

Please can you see on the attached Excel, if this is what your result to be.

Regards,
Rev12




If helpful to you;
- Please click on the *Add Reputation button at the bottom left.

Then mark your thread as SOLVED if meets your need:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

If you agree, please add the detail requested to post #6 above. Thanks.