Need assistance with Index Match

[it's an excel world]

Hello all,

I am trying to write a formula that performs a partial search in column P identify the tab to pull some data back using the index match function. Here is what I have so far. I'm not sure what I am doing wrong, but the when I copy the formula down the file it appears not to make it past the first IF. If Bike does not appear in column P, I receive the # Value! error.

=IF(SEARCH("Bike",P2),INDEX(Bike!$B$2:$K$151,MATCH(RawData!BK2,Bike!$A$2:$A$151,0),MATCH(RawData!BJ2,Bike!$B$1:$K$1,0)),IF(SEARCH("Scooter",P2),INDEX(Scooter!$B$2:$H$152,MATCH(RawData!BK2,Scooter!$A$2:$A$152,0),MATCH(RawData!BJ2,Scooter!$B$1:$H$1,0)),IF(SEARCH("Car",P2),INDEX(Car!$B$2:$H$152,MATCH(RawData!BK2,Car!$A$2:$A$152,0),MATCH(RawData!BJ2,Car!$B$1:$H$1,0)),"")))

Thanks in advance!

 

[jasonb75]

That's because if "Bike" isn't found in P2 then search will retuen an error, try

IF(ISNUMBER(SEARCH("Bike",P2)),...

Or alternatively use a different function, for example

=IFERROR(CHOOSE(MATCH(P2,{"Bike","Scooter","Car"},0),INDEX(Bike!$B$2:$K$151,MATCH(RawData!BK2,Bike!$A$2:$A$151,0),MATCH(RawData!BJ2,Bike!$B$1:$K$1,0)),INDEX(Scooter!$B$2:$H$152,MATCH(RawData!BK2,Scooter!$A$2:$A$152,0),MATCH(RawData!BJ2,Scooter!$B$1:$H$1,0)),INDEX(Car!$B$2:$H$152,MATCH(RawData!BK2,Car!$A$2:$A$152,0),MATCH(RawData!BJ2,Car!$B$1:$H$1,0))),"")