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

#### Mw911

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

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

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

Really appreciate any help!

Kind regards,
MW

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Please post your formulas. You can wrap a VLOOKUP in IFERROR to suppress #N/A, eg:

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

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))),"","")

Is this what you want?

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

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?

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

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

Replies
3
Views
256
Replies
13
Views
659
Replies
1
Views
147
Replies
1
Views
102
Replies
12
Views
716

### Forum statistics

1,221,052
Messages
6,157,630
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

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