VLOOKUP with 3 columns and blank cell

mdesjardins99

New Member
Joined
Dec 21, 2016
Messages
4
Hello Everyone,

I have used the VLOOKUP formula in the past quite often but this time I am a bit confused as to which formula I should use to get the task done.

I have attached some screen shots to hopefully make everything clear.


As you can see in the first picture I need to select a product from cell B18 and then when I do CELLS- E18/F18/G18/H18 are automatically generated with the corresponding data from my table.


the problem that I have is that I used to have only one column for the data in H18 but now that LEED has change a few things I have a choice
between 3 columns of data as you can see on the second image.

So if we look at image 2 and 3 we can see that there is 3 different types of Rated Life data , there will only be 1 cell with a value at any point,
but it's not always going to be the first column, the data could be in the second or third column, so 1 cell with actual data and 2 cell with a blank





which formula should I use that will serve as a LOOKUP but at the same time figure out which cells are empty and give me the result on the cell that actually has data, if blank then I need the cell to stay blank .

Hopefully I made myself clear enough :)

Thank you for the feedback and let me know if I can clarify in anyway!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi, welcome to the board :)

1. Your images don't seem to have come though?
2.. Not everyone can see images anyway, so could you provide some sample data?
 
Upvote 0
Thank you for the welcome!

Ok so let's say when I select a product from my drop down list I want to display the only column that has a value in it, the problem with VLOOKUP is that I need to specify only 1 column to look into.


Products Column 1 Column 2 Column 3

Car 1 1000 (Blank Cell) (Blank Cell)
Car 2 (Blank Cell) (Blank Cell) 1500
Car 3 (Blank Cell) 1200 (Blank Cell)


Ok so as you can see when I select Car 2 from the drop down list I want the formula to understand that I selected Car 2 and to look in of the 3 columns and display the only cell that has a value in it whether is it in column 1, 2 or 3.

There will never be more than one cell with a value in it, at no point you'll have a ''car'' with multiples values across columns.

Hopefully this is enough information :)

Thank you,
 
Upvote 0
Sorry about the formatting on the previous message and I can't seem to edit my post ..
So here it is again.

Thanks,

Products.........Column 1................Column 2...............Column 3


Car 1..............1000....................(Blank Cell).............(Blank Cell)
Car 2..........(Blank Cell)...............(Blank Cell).................1500
Car 3..........(Blank Cell)...................1200..................(Blank Cell)
 
Upvote 0
How about something like this:

=IFERROR(VLOOKUP(lookup_value,lookup_array,no_of_col_I,0),"")&IFERROR(VLOOKUP(lookup_value,lookup_array,no_of_col_J,0),"")&IFERROR(VLOOKUP(lookup_value,lookup_array,no_of_col_K,0),"")

If only one of the three is ever populated, then the concatenation will return the correct value without trailing or leading spaces.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top