+ Reply to Thread
Results 1 to 4 of 4

IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Cape Town, South Africa
    MS-Off Ver
    2013
    Posts
    9

    IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)

    Solved by Mackers here http://www.mrexcel.com/forum/excel-q...ml#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".

    {{{for some reason the tables don't seem to be formatting properly so i have added them in a word attachment}}}

    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
    Attached Files Attached Files
    Last edited by Emile du Toit; 08-20-2016 at 07:31 AM. Reason: Solved

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,646

    Re: IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)

    A Word document isn't much use.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,646

    Re: IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  4. #4
    Registered User
    Join Date
    08-16-2016
    Location
    Cape Town, South Africa
    MS-Off Ver
    2013
    Posts
    9

    Re: IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)

    Brilliantly solved by Mackers here http://www.mrexcel.com/forum/excel-q...ml#post4610302

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index Match with nested isnumber
    By JamesArmitage in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-22-2016, 08:04 AM
  2. One formula using IF; ISERROR; INDEX; OFFSET; MATCH.
    By des333 in forum Excel General
    Replies: 9
    Last Post: 08-20-2015, 08:37 AM
  3. Using ROWS, Index and Match - out of my depth
    By Michael3011 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-17-2015, 04:28 PM
  4. Replies: 2
    Last Post: 06-29-2013, 11:58 PM
  5. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  6. IF(ISERROR) with Index/Match formula
    By ninuskka in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-07-2011, 02:19 PM
  7. Using Search with either vlookup or match and index
    By jlowenstein in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1