# VLOOKUP with 3 columns and blank cell

#### mdesjardins99

##### New Member
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### FDibbins

##### Well-known Member
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?

#### mdesjardins99

##### New Member
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,

#### mdesjardins99

##### New Member
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)

#### AliGW

##### Banned

=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:

#### mdesjardins99

##### New Member
Thank you so much it works perfectly :D !!!

You're welcome!

Replies
7
Views
209
Replies
1
Views
128
Replies
3
Views
282
Replies
4
Views
373
Replies
1
Views
143

1,191,723
Messages
5,988,308
Members
440,148
Latest member
sandy123

### 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.

### Which adblocker are you using?

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

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