Trying to return multiple "values" in one cell based on suffixes in multiple cells

[Mw911]

Hi all,

This is a bit of a mind bender (to me). The title may not seem so coherent either.

I've got something sorted like below:

..| A | B | C | D |
1 |AB | AC | AD | AB -
AC -
AD -

Then in another sheet I have a table of what the values are like below:
AB | Means this
AC | Means that
AD | Means etc

At the minute I've got three Vlookup formulas in cell "D" which looks at cells A & B & C and then returns their respective values from the second sheet.

However, it seems that once one of these columns is empty and for instance I only have something in A1 then it returns an N/A error =/

I'm just wondering if there was an easier way to go about this?

Really appreciate any help!

Kind regards,
MW

 

[Andrew Poulsom]

Please post your formulas. You can wrap a VLOOKUP in IFERROR to suppress #N/A, eg:

=IFERROR(VLOOKUP(A1,B:C,2,FALSE),"")

 

[Mw911]

Apologies,

The formula I have is as follows:
=IFERROR(VLOOKUP(M4,Suffix,2,FALSE),"")&IF(ISNA(VLOOKUP(M4,Suffix,2,FALSE)),"",",
"&VLOOKUP(N4,Suffix,2,FALSE))&IF(OR(ISNA(VLOOKUP(M4,Suffix,2,FALSE)),ISNA(VLOOKUP(N4,Suffix,2,FALSE))),"","")

 

[Andrew Poulsom]

Is this what you want?

=IFERROR(VLOOKUP(M4,Suffix,2,FALSE),"")&IFERROR(VLOOKUP(N4,Suffix,2,FALSE),"")

 

[Mw911]

Andrew, it seems that if I drastically cut down my formula to just IFERROR(Vlookup(1)) & " "&IFERROR(Vlookup(2)) it seems to work great! However, I was just wondering if there were an easier way to do this considering I will have 9 different columns to v lookup?

 

[Andrew Poulsom]

There isn't an easier way because Excel is hopeless when it comes to concatenation.

 

[Mw911]

Ah, that's unfortunate! Hopefully I don't reach the limit for just multiplying this formula out fo each of the other columns..

Thanks for your help!