Search Function for String with Forward Slash

[T86157]

Is there a work around for Search() function to find an array, or column of string cells that includes forward slashes ("/")?

[Pete_UK]

What do you mean by "work around"? You can use SEARCH to look for "/". Please give some more detail of what you are trying to achieve.

Pete

[T86157]

I was trying to use the search function nested within Index. The range, or column of text contains different text, a forward slash, and more text in each row for that column. I'm trying to use this column of data to reference it to an adjacent column that contains that text. If I type "Open/Closed" in A1 it returnes #value or #N/A. I tried using Shift + Enter to enter the formula as an array but that does not seem to work either.

Please Login or Register  to view this content.

where Range1 is a column of data adjacent to the column of A1 and Range2 is adjacent to the right of column Range1.

[Pete_UK]

So you are trying to find "Open/Closed" in column B and return the corresponding value from column C? If so, then you can use this formula:

=IFERROR(VLOOKUP($A$1,$B:$C,2,0),"not found")

No need to enter this as an array formula.

Hope this helps.

Pete

[T86157]

I'm still getting "not found" or #N/A if I take out IFERROR()
This is part of the data I am searching. I'm trying to find "Open/Closed" in Column C and return the corresponding value from Column B.

ColB ColC
BDC1 Unlocked/Locked
BDC2 Trouble/Normal
BDC3 Open/Closed
BDC4 Request To Exit/Normal
BDC5 On/Off
BDC6 Closed/Open
BDC7 Enabled/Disabled
BDC8 Fire/Off
BDC9 Heating/Cooling
BDC10 Lead/Lag
BDC11 On/Standby
BDC12 Stop/Start
BDC13 Start/Stop
BDC14 Summer/Winter
BDC15 Tripped/OK
BDC16 Occupied/Unoccupied
BDC17 Yes/No
BDC18 High Speed/Low Speed
BDC19 Open/Off
BDC20 Close/Off
BDC21 Running/Stopped
BDC22 English/Metric

[salim ali]

try this file

[Tony Valko]

Maybe this...

Data Range

	B	C	D	E	F
1
2	BDC1	Unlocked/Locked	------	Open/Closed	BDC3
3	BDC2	Trouble/Normal
4	BDC3	Open/Closed
5	BDC4	Request To Exit/Normal
6	BDC5	On/Off
7	BDC6	Closed/Open
8	BDC7	Enabled/Disabled
9	BDC8	Fire/Off
10	BDC9	Heating/Cooling
11	BDC10	Lead/Lag
12	BDC11	On/Standby
13	BDC12	Stop/Start
14	BDC13	Start/Stop
15	BDC14	Summer/Winter
16	BDC15	Tripped/OK
17	BDC16	Occupied/Unoccupied
18	BDC17	Yes/No
19	BDC18	High Speed/Low Speed
20	BDC19	Open/Off
21	BDC20	Close/Off
22	BDC21	Running/Stopped
23	BDC22	English/Metric

This formula entered in F2:

=INDEX(B2:B23,MATCH(E2,C2:C23,0))

[T86157]

The nested Match formula inside Index worked. Not sure why it wasn't working earlier. When I used the Match nested within an Index, it would return an output of "#N/A" for all values except the first/top-most one in the column.

[Tony Valko]

Good deal. Thanks for the feedback!