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
Hello
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
listening..
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
Okay.
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
yes
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.