Excel - How to Use a VLOOKUP Formula - Expert Solution

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

I need a formula where I have to use a vlookup but i am looking at two columns in the table array, if one of the does not have any number in it then I need the the other column to return me an answer
Solved by K. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 21/05/2018 - 06:53
Excelchat Expert 21/05/2018 - 06:53
Welcome to excelchat.
User 21/05/2018 - 06:53
Hi thank you
Excelchat Expert 21/05/2018 - 06:53
I understand you want help with a vlookup formula. Please tell me more to get the full understanding of the problem.
Excelchat Expert 21/05/2018 - 06:54
Some sample data or the file you are working with would help a lot to understand the problem as well.
User 21/05/2018 - 06:54
Unfortunately I cannot share the data but I will try to explain should not be very hard
User 21/05/2018 - 06:55
SO I am trying to look up a value and there are two columns in the table array that I am interested in.
Excelchat Expert 21/05/2018 - 06:55
User 21/05/2018 - 06:56
So I am trying to lookup costs of certain companies and in the table array i have costs of 2017 and 2016. Initially, I am interested for the formula to give me back the costs for 2017 but in some cases there are no costs for 2017 and then I would like the formula to give me back costs of 2016
User 21/05/2018 - 06:57
it has to be with an IF I think but not sure how to get it
Excelchat Expert 21/05/2018 - 06:58
I understand what you need here, although this is not enough to write a necessary formula. I need to see at least you can provide some dummy data that replicates your original file.
Excelchat Expert 21/05/2018 - 06:59
Yeah, an IF formula would be the solution. IF(cell=empty,vlookupfor2016,vlookup2017) would be the logic.
User 21/05/2018 - 07:00
cell empty i just write it like that in the formula or is it " "
Excelchat Expert 21/05/2018 - 07:01
My apologies, excel equivalent to empty is "" so you are correct. You have to write =IF(cell="",2016vlookup,2017vlookup)
Excelchat Expert 21/05/2018 - 07:02
That means, when 2017 column is empty, do a vlookup with 2016, otherwise do a vlookup with 2017.
User 21/05/2018 - 07:02
ok let me try
Excelchat Expert 21/05/2018 - 07:02
sure, take your time.
User 21/05/2018 - 07:06
=IF(cell=" ";VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;5;0);VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F6;6;0))
User 21/05/2018 - 07:06
returns me nothing
User 21/05/2018 - 07:06
but "name" in the cell
Excelchat Expert 21/05/2018 - 07:07
=IF(cell="";VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;5;0);VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F6;6;0)) please try this, there was a space which shouldn't be in there, and replace "cell" with the cell reference of your actual file.
User 21/05/2018 - 07:09
and replace "cell" with the cell reference of your actual file--> this means that I substitute the cell in the column of 2017 costs?
Excelchat Expert 21/05/2018 - 07:09
The formula should be like this =IF(B2="",VLOOKUP(C2,H2:I3,2,false),VLOOKUP(C2,E2:F3,2,false))
Excelchat Expert 21/05/2018 - 07:11
The cell reference should be the cell which might be empty. So, it might be the column of 2017 costs but I can't say for sure since I don't the actual data. The formula above I gave should be helpful.
Excelchat Expert 21/05/2018 - 07:11
Let me know if you still have doubt's.
User 21/05/2018 - 07:12
the cell " ", are u refereing to a cell in the table array
User 21/05/2018 - 07:12
Excelchat Expert 21/05/2018 - 07:12
Okay, please take a look at the preview window. Can you see what I wrote there?
Excelchat Expert 21/05/2018 - 07:14
Hello, are you there?
Excelchat Expert 21/05/2018 - 07:14
If you can see the preview window, I will explain it.
User 21/05/2018 - 07:15
Just one sec I am trying it
Excelchat Expert 21/05/2018 - 07:15
User 21/05/2018 - 07:15
previous window u mean the formula u sent or what
Excelchat Expert 21/05/2018 - 07:16
Preview window, the spreadsheet on the right hand side of this chat box.
User 21/05/2018 - 07:17
ok I can see it
Excelchat Expert 21/05/2018 - 07:17
Assume column B is 2017 and column C is 2016.
User 21/05/2018 - 07:18
does it matter that my table array is in another sheet
Excelchat Expert 21/05/2018 - 07:18
So, the formula that we write here is =IF(B2="",VLOOKUP(C2,H2:I3,2,false),VLOOKUP(C2,H2:J3,3,false))
Excelchat Expert 21/05/2018 - 07:18
No it shouldn't.
Excelchat Expert 21/05/2018 - 07:18
The formula makes sense/
Excelchat Expert 21/05/2018 - 07:18
User 21/05/2018 - 07:19
yes it does i dont know why i dont get an answer tho
Excelchat Expert 21/05/2018 - 07:19
What is the formula you are using now?
User 21/05/2018 - 07:19
=IF('Logistics costs'!F2=" ";VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;5;0);VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;6;0))
Excelchat Expert 21/05/2018 - 07:22
I'm sorry I really can't say what is wrong with the formula unless I can take a look at the data. The best I can do is Guessing what is wrong.
User 21/05/2018 - 07:22
Okay, thanks
Excelchat Expert 21/05/2018 - 07:27
I'm sending a formula, please try it quickly.
Excelchat Expert 21/05/2018 - 07:28
=IF(VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;5;0)="";VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;6;0);VLOOKUP(Table8[@[Identifier (mill&payerno)]];'Logistics costs'!A:F;5;0))
User 21/05/2018 - 07:29
yes it is working, not sure abobut the numbers will double check it
User 21/05/2018 - 07:29
but what was wrong?
Excelchat Expert 21/05/2018 - 07:34
Still problem?
Excelchat Expert 21/05/2018 - 07:35
I need to know otherwise I can't help.
Excelchat Expert 21/05/2018 - 07:38
I guess no problem then. But if you are still struggling, I have revised the preview window and the formula now is =IF(VLOOKUP(F3,A3:C4,2,false)="",VLOOKUP(F3,A3:C4,3,false),VLOOKUP(F3,A3:C4,2,false)) >> you can look at the data for reference.
Excelchat Expert 21/05/2018 - 07:39
The chat will get disconnected in 14 minutes. If you think You need to clear doubt's you can ask me now.
User 21/05/2018 - 07:40
Okay I think that worked but
User 21/05/2018 - 07:40
instead of first returning 2016 i need 2017
User 21/05/2018 - 07:40
because the formula u gave me says that if the cell where we have 2016 is empty give 2017
User 21/05/2018 - 07:40
and i need it the other way around
User 21/05/2018 - 07:41
but when i just changed the column index
User 21/05/2018 - 07:41
i dont have answers for all cells
Excelchat Expert 21/05/2018 - 07:41
Okay, I don't know your data but I can send you the formula for the preview window.
User 21/05/2018 - 07:41
Excelchat Expert 21/05/2018 - 07:43
=IF(VLOOKUP(F3,A3:C4,2,false)="",VLOOKUP(F3,A3:C4,3,false),VLOOKUP(F3,A3:C4,2,false)) Here it is. The formula will first check VLOOKUP(F3,A3:C4,2,false)="" which is whether 2017 is empty or not. IF empty then VLOOKUP(F3,A3:C4,3,false) gives 2016 cost, otherwise VLOOKUP(F3,A3:C4,2,false) gives 2017 cost.
User 21/05/2018 - 07:44
Thank you
Excelchat Expert 21/05/2018 - 07:45
I just glad that we were able to sort out the problem.
Excelchat Expert 21/05/2018 - 07:45
Let me know if you still have problem.
Excelchat Expert 21/05/2018 - 07:50
The session will end now, thanks for your patience. Have a great day ahead1

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc