Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Working with two workbooks, the formula I need is going to look up the value in workbook 1 in a large range in workbook 2 (columns a-c and if it finds it and it meets a specific value in column 2, then bring back the value in column 3. How do I achieve this? Workbook 2 columns 2 and 3 are alphanumeric
Solved by I. J. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 02/08/2018 - 01:16
Hello
Excelchat Expert 02/08/2018 - 01:16
Welcome
User 02/08/2018 - 01:16
Hi
User 02/08/2018 - 01:16
I'm hoping you can help me
Excelchat Expert 02/08/2018 - 01:17
I understand you need help with lookup, in order to help you out, I need to look at the data. Can you send me the files?
User 02/08/2018 - 01:17
I can't as I am working remotely from home on a file from the office.
User 02/08/2018 - 01:18
Can I set up an example here?
Excelchat Expert 02/08/2018 - 01:19
Well, that would be great.
Excelchat Expert 02/08/2018 - 01:19
I'm sorry that I couldn't get much from the description you provided. It is essential to know the data for lookup problem.
User 02/08/2018 - 01:21
SO, ON SHEET 1 I HAVE THE NUMBER I NEED TO LOOK UP. SHEET 2 SHOWS THE NUMBER I AM LOOKING UP, AND I WANT IT TO BRING BACK THE VALUE IN COLUMN C IF COLUM B MEETS THE EXACT DESCRIPTION "TARIFFDESC"
Excelchat Expert 02/08/2018 - 01:22
I'm sorry, I need more than what you have setup there.
User 02/08/2018 - 01:23
THEN IF B=TARIFFDESC, BRING BACK VALUE IN C
Excelchat Expert 02/08/2018 - 01:24
Sorry again, what is that?
Excelchat Expert 02/08/2018 - 01:24
You said "THEN" but didn't mentioned what was before then.
User 02/08/2018 - 01:25
Do you not see the part of the fomula that I started? I have a document of 94000 rows to search for TARIFFDESC and bring back a value
Excelchat Expert 02/08/2018 - 01:26
I'm sorry if you are feeling frustration, I don't see any formula, what I see is you wrote @vlookup, that can mean anything.
Excelchat Expert 02/08/2018 - 01:27
Now, I understand you meant to say apply vlookup formula.
User 02/08/2018 - 01:28
ok....@vlookup(the number, the range, then I need to search by certain criteria "TARIFFDESC" and bring back the value next to it.
Excelchat Expert 02/08/2018 - 01:32
Okay, after giving it a great amount of though, I understand you meant to say if if sheet2 have the lookup values of column and in another column have "TARIFFDESC" then import what is in third column
Excelchat Expert 02/08/2018 - 01:32
Confirm that my analysis is correct or not.
User 02/08/2018 - 01:33
yes, correct
Excelchat Expert 02/08/2018 - 01:34
Sometimes it helps if you just describe and show the data without mentioned what formula to apply because what you are thinking might not be the solution. As an example, VLOOKUP won't work in your case.
User 02/08/2018 - 01:34
What will work?
Excelchat Expert 02/08/2018 - 01:34
I will write a formula
Excelchat Expert 02/08/2018 - 01:36
INDEX(Sheet2!C:C,match(1,(Sheet2!A:A=A1)*(Sheet2!B:B="TARIFFDESC"),0))
Excelchat Expert 02/08/2018 - 01:36
This is the formula you need.
User 02/08/2018 - 01:37
Thank you. I'm trying to understand it. Can you explain?
Excelchat Expert 02/08/2018 - 01:37
Sure
Excelchat Expert 02/08/2018 - 01:38
INDEX(Sheet2!C:C mean we are importing from Sheet2 column C.
Excelchat Expert 02/08/2018 - 01:39
You don't need to worry about ",match(1," part of the formula. Next is (Sheet2!A:A=A1)*(Sheet2!B:B="TARIFFDESC")
Excelchat Expert 02/08/2018 - 01:41
Now (Sheet2!A:A=A1) means we are comparing sheet2 column A against the value of A1 and (Sheet2!B:B="TARIFFDESC") means we are comparing sheet2 column B for TARIFFDESC
Excelchat Expert 02/08/2018 - 01:41
Finally the 0 at the end of the formula means it has to be an exact match.
User 02/08/2018 - 01:41
I'll give that a try. Thank you for your time. It's truly appreciated.
Excelchat Expert 02/08/2018 - 01:42
There is another thing.
Excelchat Expert 02/08/2018 - 01:42
You have to press control+shift+Enter when you input the formula in a cell, that is because the formula I wrote is an array formula, not a standard formula.
Excelchat Expert 02/08/2018 - 01:43
Now, you can ask me if you have any question.
User 02/08/2018 - 01:44
so when I enter the formula, I have to hit control+shift and enter for it to work? Can the formula be copied down a page?
Excelchat Expert 02/08/2018 - 01:45
You are correct, hit control+shift+enter at once. And yes, you can copy the formula/ drag the formula as usual.
User 02/08/2018 - 01:45
Thank you. I hope it works.
Excelchat Expert 02/08/2018 - 01:45
It will work.
User 02/08/2018 - 01:45
Have a great day. Thanks again!!!
Excelchat Expert 02/08/2018 - 01:46
Thank you too. Enjoy a great time!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.