Formula to Replace Text with Numerical Value (if this, then that?) from VLOOKUP

[jrjobe]

I've been racking my brain on this for a while and I can't figure this out. I have two worksheets. WS1 (LIST) uses VLOOKUP to pull pricing over from WS2 (Cobra-Import) One of the columns in WS2 is the unit quantity column (E), however, instead of numbers, the system that sends these out uses letters. I need to somehow convert those letters to a numerical value to be used on the LIST worksheet.

For the UNIT QTY column on the LIST worksheet, I am using this formula:

=IFERROR(VLOOKUP(H3,'Cobra-Import'!A:L,5,FALSE),0)

- Column H has the UPC that will find the match on the Cobra worksheet and assign the appropriate value from column E.

As of right now, the formula is doing exactly what it's supposed to do, which is pulling in what is in Column E of the Cobra worksheet. Those values are either E, C, or M depending on the item. I need those values to be replaced with:

E: 1
C: 100
M: 1000

Trying to figure out some kind of "If this, then that" type of formula so it assigns the appropriate numerical value.

In another column of the LIST worksheet, I am also using a formula to calculate the material price per unit, which needs the UNIT QTY to be in numerical form unless there is a way to calculate E, C, or M in this column. Here is the formula:

=((J3/K3)+(N3/O3))

- Column J is UPC 1 and Column N is UPC two. K and O is the Unit Quantity for the respective UPC. The reason for two UPCs is that some items we include another item in the quoted price.

If anyone has any ideas, it will be greatly appreciated!

 

[Toadstool]

Hi JRJobe,

I wasn't sure how you wanted to handle the two error conditions so I've trapped each:

1. No match on UPC
2. Column E of UPC not E, C or M

JRJobe.xlsx
	A	B	C	D	E
1	UPC				Value
2	01234 56789				E
3	22222 33333				C
4	33333 44444				M
5	92929 92929				C
6	72727 45454				X
7
Cobra-Import

JRJobe.xlsx
	H	I	J
1
2	UPC		Result
3	01234 56789		1
4	22222 33333		100
5	33333 44444		1000
6	11111 11111		No match
7	72727 45454		Not E, C or M
LIST

Cell Formulas
Range	Formula
J3:J7	J3	=IFERROR(INDEX({1,100,1000,"No match"},MATCH(IFERROR(VLOOKUP(H3,'Cobra-Import'!A:L,5,FALSE),"No match"),{"E","C","M","No match"},0)),"Not E, C or M")

 

[jrjobe]

@Toadstool you are awesome! Like I said, I've been racking my brain on this and I wasn't even close--kept getting a pop-up error that was formula needed more input. As for error handling, the way you have it will suffice. At least if a line item drops the unit quantity from the master, it will be noticeable. I ran a test with it and worked like a champ! Everything is pulling over the way it should now. Can't thank you enough!

 

[Toadstool]

You're welcome and Thanks for the feedback.

I was going for a CHOOSE and SEARCH method but the INDEX and MATCH seemed more readable.

JRJobe.xlsx
	H	I	J
1
2	UPC		Result
3	01234 56789		1
4	22222 33333		100
5	33333 44444		1000
6	11111 11111		No match
7	72727 45454		Not E, C or M
LIST (2)

Cell Formulas
Range	Formula
J3:J7	J3	=IFERROR(CHOOSE(SEARCH(IFERROR(VLOOKUP(H3,'Cobra-Import'!A:L,5,FALSE),0),"0ECM"),"No match",1,100,1000),"Not E, C or M")

 

[jrjobe]

Both work and in fact, I'll just use both to really confuse the next guy that comes along to work on this . In reality, since I have two sets of UPC info (Unit 1 QTY and Unit 2 QTY) for line items that we include two different UPCs, I included both for future reference. I did have to change "No Match" to a number otherwise the other cell that uses a formula to calculate the final per quantity price gives a value error.

In this case, I just changed Unit 1 Qty to use 8888 and Unit 2 Qty to 9999 in place of "No match", then used conditional formatting to "mask" them. I could have used any number, but those are what came to mind and don't interfere with the final price. I might revisit the conditional formatting, but some of the old guys that use this will just get lost if they see numbers that might seem out of place to them. Doing it this way keeps the worksheet a little cleaner.

 

[Toadstool]

You could always put a text "0" as the IFERROR and check if you avoid the VALUE, then you could use TYPE to check what the result is and highlight the challenged values.

JRJobe.xlsx
	H	I	J
1
2	UPC		Result
3	01234 56789		1
4	22222 33333		100
5	33333 44444		1000
6	11111 11111		0
7	72727 45454		0
LIST (2)

Cell Formulas
Range	Formula
J3:J7	J3	=IFERROR(CHOOSE(SEARCH(IFERROR(VLOOKUP(H3,'Cobra-Import'!A:L,5,FALSE),0),"0ECM"),"0",1,100,1000),"0")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J3:J12	Expression	=TYPE(J3)=2	text	NO