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

[Emile du Toit]

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

	B	C	O	R (COLUMN BEING INPUTTED)
1			CVSNVT78	1 YEAR WARRANTY
2			CVSNVT5590	90 DAY WARRANTY
3			CVSTRU9111
4			CVSNVT5678	2 YEAR WARRANTY

<tbody>
</tbody>

ALL SUPPLIERS FINAL BEFORE DELE

	B	C	D	O
1				CVSNVT78
2				CVSNVT5590
3				CVSTRU9111
4				CVSNVT5678

<tbody>
</tbody>

UPDATED SKU PRICE QUANTITY

	B	G	J	K
1		CVSNVT4020	1 YEAR WARRANTY
2		CVSNV5678	2 YEAR WARRANTY
3		CVSNVT78	1 YEAR WARRANTY
4		CVSNVT69002	3 YEAR WARRANTY

<tbody>
</tbody>

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

 

[Mackers]

Hi

I think you're close with your current formula. You want the AND() to wrap the first two arguments, as it doesn't work like English where you say "A and B", in Excel you have to say "AND(A,B)". You can also slightly adjust the logic as follows:

=IF(ISNUMBER(SEARCH("CVSNVT",'ALL SUPPLIERS FINAL BEFORE DELE'!O2)),IF(ISNUMBER(MATCH('ALL SUPPLIERS FINAL BEFORE DELE'!O2,'Updated SKU Price Quantity'!$G$2:$G$50000,0)), 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)),"90 Day Warranty")

I've slightly changed the ordering, as in this instance I'm saying if the MATCH() is a number, do the INDEX(MATCH()), otherwise return "90 Day Warranty". I've also omitted using an AND, instead opting for what's called a 'nested if'. This means I'm using two sets of if statements (in this instance) to determine which outcome I want based on several true/false checks. It works like this: IF(first test is true, IF(second test is true, do this [both are true], otherwise do this [first is true, second is false]), otherwise do this [both are false])

Note that you can also use =COUNTIF('ALL SUPPLIERS FINAL BEFORE DELE'!O2,"*CVSNVT*") to check if the cell contains that string: if it returns a 1 or higher it contains the string. This means you can plug it into the IF() argument without any modifier (as any number over 1 is considered TRUE and a 0 is considered FALSE).

Hope that helps - let me know if it still isn't working.

Mackers

 

[Joe4]

Cross-posted here: IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth
and here: if isnumber search and iserror vlookup index match - used together.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Emile du Toit]

Hi Mackers,

Thanks for the solution! I was a bit concerned that it returns 'FALSE' when "CVSNVT" isn't present (rather than just blank. But when I add in other IF formulas (using CONCATENATE) to provide rules for other cells it isn't adding FALSE to their solution text, which is perfect. Thanks so much!