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

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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:
Excel 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:
Excel 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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,430
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
1UPCValue
201234 56789E
322222 33333C
433333 44444M
592929 92929C
672727 45454X
7
Cobra-Import


JRJobe.xlsx
HIJ
1
2UPCResult
301234 567891
422222 33333100
533333 444441000
611111 11111No match
772727 45454Not E, C or M
LIST
Cell Formulas
RangeFormula
J3:J7J3=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")
 
Solution

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
@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

Well-known Member
Joined
Mar 5, 2018
Messages
2,430
Office Version
  1. 2016
Platform
  1. Windows
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
HIJ
1
2UPCResult
301234 567891
422222 33333100
533333 444441000
611111 11111No match
772727 45454Not E, C or M
LIST (2)
Cell Formulas
RangeFormula
J3:J7J3=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

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
Both work and in fact, I'll just use both to really confuse the next guy that comes along to work on this :LOL:. 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

Well-known Member
Joined
Mar 5, 2018
Messages
2,430
Office Version
  1. 2016
Platform
  1. Windows
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
HIJ
1
2UPCResult
301234 567891
422222 33333100
533333 444441000
611111 111110
772727 454540
LIST (2)
Cell Formulas
RangeFormula
J3:J7J3=IFERROR(CHOOSE(SEARCH(IFERROR(VLOOKUP(H3,'Cobra-Import'!A:L,5,FALSE),0),"0ECM"),"0",1,100,1000),"0")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J3:J12Expression=TYPE(J3)=2textNO
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,666
Members
425,227
Latest member
SpreadsheetNoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top