About Bill Hermanson Expertise Please tell me WHICH EXCEL VERSION you are using!
DO NOT ASK ME me about Macros or VBA.
Please read my "instructions to questioners" in my full profile [use View Profile, at right], to help you write a question that I can understand, without having to ask you what you mean.
If your question contains any of the words THIS, IT, THAT, THOSE, or THEY, I likely won't understand IT. Please rewrite!
My Expertise: I am an expert at data manipulation, the use of incredibly complex logical statements, databases, combining tables and extracting data, all the LOGICAL, LOOKUP & REFERENCE functions, dynamic ranges, creating professional appearing spreadsheets, complex functions, integrated charts and visual displays, user interfaces.... I can make Excel do anything!
But PLEASE... NO MACRO or VBA QUESTIONS!
Experience 25 years development of complex spreadsheets for personal and professional use. I've developed hundreds (or thousands!) of spreadsheets in all fields, from complex engineering calculations to game scoring, financial analysis, scheduling, cost-of-doing-business, and analysis of home energy use. I even used Excel to assist in design of the flight computers presently on board the Hubble Space Telescope (1984-1991)
Education/Credentials BSEE Electrical Engineering, CU Boulder CO USA Use of spreadsheets since 1982 Boulder Valley School District, Life Long Learning, Instructor Owner & Operator of Excel Expert, LLC
Column B Column C Column D
Sub Code Sap Code Description
SW09 Jiffy Bag 1
SW12 Jiffy Bag 2
SW21 Jiffy Bag 3
24959 Brown Bag 12
24960 Brown Bag 15
CSP1014BOX Bubble Rap 1.2
PPS1215PR Bubble Rap 1.3
SB2053 24112 Clear Tape
SW01 21123 Security Tape
IN Sheet 2 user enters the codes in column B (which could be Sub code or Sap Code). In column c I want to lookup discription from sheet 1(column D).
User can type either Sub or Sap code which matches column B or C (sheet1)
Please can you assist me.
Many Thanks in advance.
Regards
Answer Hitesh,
You'll have to provide two input cells for your user interface. The user will have to know where to enter the code he is using. One would be the SUB code, which would match up with sheet 1 col B, and the other would be the SAP code, which would match up with sheet 1 col C.
After that, you can use a VLOOKUP formula (you'll need two, one for each possible input cell) which would retrieve the matching value. One of the VLOOKUPs will return an error, since one of the input cells will always be blank. You can use an IF statement based upon ISBLANK as the conditional test to determine which input cell is blank, and therefore which VLOOKUP to use. For example, something like this outlined function would work:
Of course you'd have to expand this to make the IF return a "" (empty cell) if BOTH SAP & SUB inputs are blank.
VLOOKUP(SAP) means to use VLOOKUP as if the input were the SAP value, and VLOOKUP(SUB) means to use VLOOKUP as if the input sere the SUB value.... I've used shorthand, you'll have to fill in the actual VLOOKUP parameters.
You could make this more complex depending on your level of Excel skills. You could have ONE input cell and let the user select (in a second cell) whether the value he's entered is a SAP or SUB value. A similar IF statement (to the outlined one I provided) could decide which VLOOKUP value to return.
Or, you could use the SAP/SUB selection to choose the col-index-num for a SINGLE VLOOKUP, and no IF statement would be needed.
I hope you understand my answer, and that it helps get you going.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
