if isnumber search and iserror vlookup index match - used together.

  • Solved by Machers here http://www.mrexcel.com/forum/e…together.html#post4610302


    Hi All,

    I have just wasted two hours trying to get this right, but to no avail. The formula I am trying to create is actually a small part of a bunch of formulas for various suppliers that I am combining in a column by using concatenate. With this in mind, I would like to avoid array formulas if at all possible, as none of the other IF statements require this.

    What I want the formula to do:
    IF a particular cell contains the part match "CVSNVT", AND i can find an exact match for this cell in a different column (column G in spreadsheet 'Updated SKU Price Quantity'), THEN to use the INDEX MATCH formula to compare this cell with that column (column G in spreadsheet 'Updated SKU Price Quantity') till it finds the match and then return the value on that row in column J in spreadsheet 'Updated SKU Price Quantity'). Otherwise, IF this particular cell contains the part match "CVSNVT", but does not contain a exact match to any cell in the other column (still column G in spreadsheet 'Updated SKU Price Quantity'), THEN to return "90 Day Warranty".


    Current
    [TABLE="width: 625"]

    [tr]


    [td][/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    O

    [/td]


    [td]

    R (COLUMN BEING INPUTTED)

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT78

    [/td]


    [td]

    1 YEAR WARRANTY

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT5590

    [/td]


    [td]

    90 DAY WARRANTY

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td]

    CVSTRU9111

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT5678

    [/td]


    [td]

    2 YEAR WARRANTY

    [/td]


    [/tr]


    [/TABLE]


    ALL SUPPLIERS FINAL BEFORE DELE
    [TABLE="width: 625"]

    [tr]


    [td][/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    O

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT78

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT5590

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    CVSTRU9111

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    CVSNVT5678

    [/td]


    [/tr]


    [/TABLE]


    UPDATED SKU PRICE QUANTITY
    [TABLE="width: 625"]

    [tr]


    [td][/td]


    [td]

    B

    [/td]


    [td]

    G

    [/td]


    [td]

    J

    [/td]


    [td]

    K

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    CVSNVT4020

    [/td]


    [td]

    1 YEAR WARRANTY

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    CVSNV5678

    [/td]


    [td]

    2 YEAR WARRANTY

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td]

    CVSNVT78

    [/td]


    [td]

    1 YEAR WARRANTY

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td]

    CVSNVT69002

    [/td]


    [td]

    3 YEAR WARRANTY

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    In the example tables you will see that I have filled in correct extracted text for three cells, and the other would remain blank (other formulas extract the currently blank text). With column R, row 1 and 4 (Current table) the cells match BoTH the part text CVSNVT AND the text in the 'O' column of their row EXACTLY matches with a cell in column G of the third table. Therefore for R1 and R4 I apply the INDEX MATCH formula to extract this information from column J of the third table. In the case of column R row 2 (Current) the part text CVSNVT does match, but the text in the 'O' column of their row does not match EXACTLY with a cell in column G of the third table. Therefore, the IF FALSE part of the equation is applied and the words "90 day warranty" are added. With Column R row 3 the text in column 'O' (of spreadsheet 1 or 2 - doesn't matter I think) is not a part match for "CVSNVT", and therefore nothing should be done in that cell.


    The formula I constructed is below. However, it does not work and i really cannot figure out why.

    =IF(isnumber(search("CVSNVT",'ALL SUPPLIERS FINAL BEFORE DELE'!O2)),AND(ISERROR(VLOOKUP('ALL SUPPLIERS FINAL BEFORE DELE'!O2,'Updated SKU Price Quantity'!$G$2:$G$50000,1,FALSE))),"90 Day Warranty",INDEX('Updated SKU Price Quantity'!$J$2:$J$50000,MATCH('ALL SUPPLIERS FINAL BEFORE DELE'!O2,'Updated SKU Price Quantity'!$G$2:$G$50000,0)))


    I would REALLY REALLY appreciate a solution as it is holding up everything else I need to create!


    Thank you in advance.
    Emile

  • Re: if isnumber search and iserror vlookup index match - used together.


    [cp]*[/cp]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules