Index/Match Help - matching value, when there is a random space added before/after

[chrono2483]

Hello, Hoping to narrow down to a specific issue I'm running into:

I am working with 2 sheets, a VIEW and PASTE. I paste time interval of data into PASTE, and use the following formula within VIEW to pull specific data, based on the interval the user selects (via a DV List)

This is the formula I am working with:

=IFERROR(IF($G$8="Elec Eng",OFFSET(INDEX('Paste'!$B:$B,MATCH($C$13,'Paste'!$B:$B,0)),2,0,,),IF($G$8="Elec Fre",OFFSET(INDEX('Paste'!$C:$C,MATCH($C$13,'Paste'!$C:$C,0)),2,0,,),IF($G$8="Home Eng",OFFSET(INDEX('Paste'!$E:$E,MATCH($C$13,'Paste'!$E:$E,0)),2,0,,),IF($G$8="Home Fre",OFFSET(INDEX('Paste'!$D:$D,MATCH($C$13,'Paste'!$D:$D,0)),2,0,,),IF($G$8="Home Care Eng",OFFSET(INDEX('Paste'!$F:$F,MATCH($C$13,'Paste'!$F:$F,0)),2,0,,),IF($G$8="Home Care Fre",OFFSET(INDEX('Paste'!$G:$G,MATCH($C$13,'Paste'!$G:$G,0)),2,0,,),IF($G$8="Support Eng",OFFSET(INDEX(' Paste'!$H:$H,MATCH($C$13,'Paste'!$H:$H,0)),2,0,,),IF($G$8="Support Fre",OFFSET(INDEX('Paste'!$I:$I,MATCH($C$13,'Paste'!$I:$I,0)),2,0,,),"")))))))),"")

$C$13/$C$14 = the time intervals selected from the DV List

The issue is that when I paste the data, some of the intervals get pasted with a space before/after (_1:00pm_, _2:00pm_, etc).

(I tried using the TRIM function and helper cells, but the index/match formula doesn't pick it up)

Is there a way to tailor the formula, to pick up those instances where there is a space, while still working with those that paste correctly???

 

[Special-K99]

It's probably not space, it's probably CHAR(160) especially if pasted from a web source.
Use SUBSTITUTE to change CHAR(160) to null "".

Use a lookup table for that formula, it should shorten the formula immensely.