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

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.

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).