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.
