VLOOKUP with 3 columns and blank cell

[mdesjardins99]

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!

 

[FDibbins]

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]

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]

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]

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.

 

[mdesjardins99]

Thank you so much it works perfectly :D !!!

 

[AliGW]

You're welcome!