ExcelBanter - simplify VLOOKUP with conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - simplify VLOOKUP with conditions (https://www.excelbanter.com/excel-worksheet-functions/127341-simplify-vlookup-conditions.html)

simplify VLOOKUP with conditions

I wrote this funciton as a quick fix for a collegue.
It looks for a comma in the cell.
No comma = standard VLOOKUP
Comma = concatenate 2 VLOOKUPs
I would like to make the formula flexible enough to handle more than 1 comma
and to handle situations when there is a slash instead of a comma.

=IF(ISERROR(FIND(",",C4,1)),VLOOKUP(C4,Cat!$A$2:$B $129,2,FALSE),VLOOKUP(LEFT(C4,(FIND(",",C4,1)-1)),Cat!$A$2:$B$129,2,FALSE)&",
"&VLOOKUP(MID(C4,FIND(",",C4,1)+1,255),Cat!$A$2:$B $129,2,FALSE))

--
tj

simplify VLOOKUP with conditions

If you posted some sample data it would help!

Biff

"tjtjjtjt" wrote in message
...
I wrote this funciton as a quick fix for a collegue.
It looks for a comma in the cell.
No comma = standard VLOOKUP
Comma = concatenate 2 VLOOKUPs
I would like to make the formula flexible enough to handle more than 1
comma
and to handle situations when there is a slash instead of a comma.

=IF(ISERROR(FIND(",",C4,1)),VLOOKUP(C4,Cat!$A$2:$B $129,2,FALSE),VLOOKUP(LEFT(C4,(FIND(",",C4,1)-1)),Cat!$A$2:$B$129,2,FALSE)&",
"&VLOOKUP(MID(C4,FIND(",",C4,1)+1,255),Cat!$A$2:$B $129,2,FALSE))

--
tj