Results 1 to 3 of 3
  1. #1
    SLC
    Guest

    Concatenation and Lookups (Excel 2000)

    I need to use lookup with a concatenation. Both the lookup_value and lookup_vector will be a concatenation.
    EX. =LOOKUP(A1&13&0,ABC!A1:A8&ABC!B1:B8&ABC!C1:C8,ABC! E1:E8)
    I'm missing something. Can I use a concatenation in as a lookup vector. Help..I'm defineitely no Excel whiz.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and Lookups (Excel 2000)

    Try this:

    =INDEX(ABC!E1:E8,MATCH(A1&13&0,(ABC!A1:ABC!A8 & ABC!B1:ABC!B8 & ABC!C1:ABC!C8),0),1)

    This is an array function and must be entered using Control+shift+Enter

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and Lookups (Excel 2000)

    Hi:
    I don't understand what you are trying to do with LOOKUP(A1&13&0,ABC!A1:A8&ABC!B1:B8&ABC!C1:C8,ABC!E 1:E8). To use the LOOKUP function (in XL 97), the Lookup Vector has to be a column or a row (as does the Result Vector). If you are trying to lookup based on a value that could be in either of three columns (ABC!A1:A8, ABC!B1:B8, or ABC!C1:C8), you would have to use conditional statements with your Lookups. The following uses the VLookup function but the same thing could be accomplished with the Lookup fn:
    =IF(ISNA(VLOOKUP(A6,D2:G15,4,FALSE)),IF(ISNA(VLOOK UP(A6,E2:G15,3,FALSE)),IF(ISNA(VLOOKUP(A6,F2:G15,2 ,FALSE)),"Failed",VLOOKUP(A6,F2:G15,2,FALSE)),VLOO KUP(A6,F2:G15,3,FALSE)),VLOOKUP(A6,F2:G15,4,FALSE) ). My data are in columns D, E, and F and the Matching Value to be returned is in column G. There are other ways of doing this by combining "lookup" type functions.
    Good Luck
    Stephen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •