Nested IF Statement in VLOOKUP To Accomplish This?

[censo]

Hello,

I have a function that examines the first three characters of a string and then executes a VLOOKUP that returns the appropriate value. That works fine.

The challenge I'm having is including in that same formula an additional check that examines characters 5-7, or the value "FRT". The positioning will always be constant and fixed if hardcoded in, or if being searched for, only that sequence of "FRT" will ever be present so I know I would not receive false positives.

What would said function look like, do I need to nest an IF statement where it searches for "FRT", if not, run the above function I already have in place?

Thanks!

Excel 2013 32 bit
	A	B	C
1	SKU	CURRENT OUTCOME	DESIRED OUTCOME
2	WAL-123456	4	4
3	WAL-FRT-123456		10
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(LEFT(A2,3),HANDLING,2,0)

Named Ranges
Name	Refers To	Cells
HANDLING	=Sheet2!$A$2:$B$3

Excel 2013 32 bit
	A	B
1	CODE	RESULT
2	WAL	4
3	FRT	10
Sheet2

 

[jim may]

Like so?...

Excel 2010

	A	B	C
1	SKU	CURRENT OUTCOME	DESIRED OUTCOME
2	WAL-123456	4	4
3	WAL-FRT-123456		10

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IF(LEN($A2)-LEN(SUBSTITUTE($A2,"-",""))=1,VLOOKUP(LEFT($A2,3),Sheet2!$A$2:$B$3,2,0),IF(LEN($A2)-LEN(SUBSTITUTE($A2,"-",""))=2,VLOOKUP(MID($A2,5,3),Sheet2!$A$2:$B$3,2,0))) C3 Copy Down <tbody> </tbody>

<tbody>
</tbody>

 

[oldbrewer]

wal-123456789

4

abc-frt-987654321

10

wal-frt-3344556677

10

abc-def-88776655

no match

formula in B1 giving 4

=IF(ISERROR(SEARCH("frt",A1)),IF(ISERROR(SEARCH("wal",A1)),"no match",4),IF(ISERROR(SEARCH("frt",A1)),"no match",10))

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>

 

[censo]

Like so?...

Thanks for the prompt reply, Jim. Your solution looks promising. Although I prefer to use a named range instead of cell references (apologies if I didn't make that clear before ... the array is way larger than what I've shown on Sheet2). With that said how would the function look like using "HANDLING" instead? I admit I'm not that skilled enough to dissect your solution to know where to make the replacement.

 

[censo]

formula in B1 giving 4

Thanks oldbrewer. Your proposed solution appears to be headed in the right direction; however, there would be many different 3 character instances in positions 1-3, not just "WAL" and so they would all need to return their associated value in column 2. Additionally, if the next set of characters in position 5-7 ("def" in the example you provided above) do not necessarily equal "FRT", then the default VLOOKUP of just the first 3 characters should execute and not generate "no match" because there will always be a match.

Basically, I'm trying to isolate the values which appear with FRT in the string and if false, default to the regular VLOOKUP that's already working. I hope this makes things clearer.

Thanks!

 

[jim may]

=if(len($a2)-len(substitute($a2,"-",""))=1,vlookup(left($a2,3),handling,2,0),if(len($a2)-len(substitute($a2,"-",""))=2,vlookup(mid($a2,5,3),handling,2,0)))

 

[Aladin Akyurek]

In B2 of Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("-"&Sheet2!$A$2:$A$3&"-","-"&$A2&"-"),Sheet2!$B$2:$B$3)

Name the range in column A of Sheet2 KEYS, the range in column B VALUES and invoke in B2 of Sheet2...

=LOOKUP(9.99999999999999E+307,SEARCH("-"&KEYS&"-","-"&$A2&"-"),VALUES)

if you like.

 

[censo]

=if(len($a2)-len(substitute($a2,"-",""))=1,vlookup(left($a2,3),handling,2,0),if(len($a2)-len(substitute($a2,"-",""))=2,vlookup(mid($a2,5,3),handling,2,0)))

Hey Jim, your solution works great!

I ran into a small problem I forgot to mention in the beginning. I found that the function errors when it encounters a subsequent dash "-" at the end of unique values. In order to make a string unique, an incrementing suffix is appended, like such:

WAL-123456 (BASE)
WAL-123456-001 (INCREMENTING SUFFIX)
WAL-123456-002 (INCREMENTING SUFFIX)
WAL-123456-003 (INCREMENTING SUFFIX)

The base and instances of FRT work awesome; however, when the function encounters any of the incrementing suffix values, it errors out. Knowing this, how can the function be modified to ignore the suffix portion?

Excel 2013 32 bit
	A	B
2	WAL-39511921	4
3	WAY-FRT-JAX1187	10
4	WAL-37302139-111	#N/A
AMZ FFIL

Cell Formulas
Range	Formula
B2	=IF(LEN($A2)-LEN(SUBSTITUTE($A2,"-",""))=1,VLOOKUP(LEFT($A2,3),HANDLING_TAB,2,0),IF(LEN($A2)-LEN(SUBSTITUTE($A2,"-",""))=2,VLOOKUP(MID($A2,5,3),HANDLING_TAB,2,0)))

Named Ranges
Name	Refers To	Cells
HANDLING_TAB	=HANDLING!$A$2:$B$31

 

[censo]

In B2 of Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("-"&Sheet2!$A$2:$A$3&"-","-"&$A2&"-"),Sheet2!$B$2:$B$3)

Name the range in column A of Sheet2 KEYS, the range in column B VALUES and invoke in B2 of Sheet2...

=LOOKUP(9.99999999999999E+307,SEARCH("-"&KEYS&"-","-"&$A2&"-"),VALUES)

if you like.

Thanks Aladin. I'll try it out.

 

[Aladin Akyurek]

Thanks Aladin. I'll try it out.

I think you should...