Expert: Bill Hermanson - 7/9/2009

Question
QUESTION: Hi,

Need help with Multi column lookup.

I have data in Sheet 1 column B, C & D.

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:

  =IF(ISBLANK(SUB-input-cell),VLOOKUP(SAP),VLOOKUP(SUB))

  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.

   >>>> Bill

---------- FOLLOW-UP ----------

QUESTION: Hi Bill

Thankyou for reply.

I typed up the formula =IF(ISBLANK(B6),(VLOOKUP(B6,'Consumables Codes'!B:F,5,FALSE)),(VLOOKUP(B6,'Consumables Codes'!C:F,4,FALSE)))

It only works on last part of formula VLOOKUP(B6,'Consumables Codes'!C:F,4,FALSE)) from example table column c value 24959 will bring over discription BUT WILL NOT WORK ON FIRST PART of the fromula value SW09 gives me #n/a

Can you guide me what i am doing wrong.

many thanks in advance.

ANSWER: Hitesh,

   A very quick look at your formula suggests that instead of:

=IF(ISBLANK(B6),(VLOOKUP(B6,'Consumables Codes'!B:F,5,FALSE)),(VLOOKUP(B6,'Consumables Codes'!C:F,4,FALSE)))


  you should have

=IF(ISBLANK(B6),(VLOOKUP(B6,'Consumables Codes'!B:F,5,FALSE)),(VLOOKUP(B6,'Consumables Codes'!B:F,4,FALSE)))

Notice the table array from the last VLOOKUP us changed from C:F to B:F.

  By the way, its not a very good idea to make VLOOKUP look through the entire columns from 1 to the end of the column.  It would be much better if you wrote

  B10:F2500

  to give the table array, instead of B:F

  I hope I have solved your problem.  As I said, i took a very quick look.  


   >>> Bill


---------- FOLLOW-UP ----------

QUESTION: Hi Bill

Could I ask you once question. I have following formula which work fine an give me the result but the ISNA part doesn't work as cell has #N/A

IF(ISNA(VLOOKUP(C9,'Consumables Codes'!$B$1:$F$200,5,FALSE)),VLOOKUP(C9,'Consumables Codes'!$C$1:$F$200,4,FALSE),VLOOKUP(C9,'Consumables Codes'!$B$1:$F$200,5,FALSE))

How to get rid of #N/A. As i know it needs =TRUE," ", but not sure where it goes.

MAny thanks for your help.

Answer
Hitesh,

 Here's your formula broken into the three main parts of an IF statement:

IF(          {beginning of the IF statement}

ISNA(VLOOKUP(C9,'Consumables Codes'!$B$1:$F$200,5,FALSE)),  {this is the conditional test}

VLOOKUP(C9,'Consumables Codes'!$C$1:$F$200,4,FALSE),     {will do this if the test is TRUE}

VLOOKUP(C9,'Consumables Codes'!$B$1:$F$200,5,FALSE))     {will do this if the test is FALSE}


  Your IF statement says that, "If a vlookup from column 5 produces a #NA error, then lookup from column 4 instead, otherwise lookup from column 5"

  I don't see anything wrong with this.

  Of course, if the lookup from column 4 ALSO produces an error, then the overall result of the IF will be an error.  You've assumed that if the column 5 lookup is bad, the column 4 lookup will be a success.

  But what if BOTH column 4 and column 5 lookups are bad?

  You can add a nested IF to check the column 4 lookup the same way you checked the column 5 lookup.  Or, you can embed the entire IF inside its own IF(ISNA() to force the return of whatever value you want if the IF returns an #NA. occurs.

   So, one way would look like this:

  =IF(ISNA(col5-VLOOKUP),IF(ISNA(col4-VLOOKUP),0,col4-VLOOKUP),col5-VLOOKUP))


  where I have abbreviated

    VLOOKUP(C9,'Consumables Codes'!$B$1:$F$200,5,FALSE))

        as simply     'col5-VLOOKUP'

   This function will return a zero if both VLOOKUPS are bad.


  And the other way would look like

  IF(ISNA(your-entire-IF-equation-goes-here),0,your-entire-IF-equation-goes-here-again)

  which is a lot more cumbersome, and which will also return a zero if both VLOOKUPS fail.

  >>> Bill