I need a (custom?) function that returns several values to one cell

[Dennis Oo]

Hi. I'm pretty much new to excel and I need a (custom?) function that returns several values to one cell:

- I have a file (File1) with product codes in column B
- I have a second file (File2) with corresponding product codes in column C
- I need the function applied to some cells of File1, column F (somewhere in the range of cell F2 to like F1000)

The result should be obtained in File2, from the row (RowX) that contains the corresponding product code

- IF cell F(RowX) is empty -> return "B2C CO Not Set"
- IF cell H(RowX) contains string "true" -> return "Set as Not Sold"
- IF cell I(RowX) contains string "true" -> return "Set as Not Sold B2C"
- IF cell J(RowX) is 0 -> return "B2C Price Not Set"

preferably in one cell (so in cell Fx of File1) like:

B2C CO Not Set
Set as Not Sold
Set as Not Sold B2C
B2C price Not Set

Or, if not possible, like: NoCO, NS, NS B2C, NO€

ELSE, no return.

I figured I could start by arranging a return for value of F(RowX), and came to this:

INDEX([FILENAME.XLS]Sheet1!$C:$C,0);MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0);6)

but I have no clue if it's correct or useful or how to proceed if it would return the correct value. After taking three hours to write the one line, I realised I'm missing basic skills..

HELP! :)

Thanks in advance,

Dennis

[#2]

Um... I guess this is what you wanted, chained if depending on your data.

=IF(ISBLANK(INDIRECT("[FILENAME.XLS]Sheet1!$F"&MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0))),"B2C CO Not Set",IF(IFERROR(FIND("true",INDIRECT("[FILENAME.XLS]Sheet1!$H"&MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0))),0)>0,"Set as Not Sold",IF(IFERROR(FIND("true",INDIRECT("[FILENAME.XLS]Sheet1!$I"&MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0))),0)>0,"Set as Not Sold B2C",IF(INDIRECT("[FILENAME.XLS]Sheet1!$J"&MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0))=0,"B2C Price Not Set",""))))

Change commas(,) for semicolons(;) if needed because i saw you were mixing them together, my OS native language uses commas but if yours uses semicolons just paste into the sheet without the equal(=) and next to it use =SUBSTITUTE(C2;",";";") (which would work against my =SUBSTITUTE(C2,",",";")) assuming you're using C2 as the formula container cell.

[#3]

@CARLOS ADRIAN

After tweaking it didn't return a valid result, but it did end up pushing me in the right direction and showing me the necessary functions, so thanks a million!

Dennis

[#4]

I'm sorry didn't solve your issue but i'm happy to have helped you to go where you wanted, btw you could have used a concatenate or & formulae so you could see all values at once instead of only one.