Results 1 to 8 of 8

Concatenate Vlookup

This is a discussion on Concatenate Vlookup within the Excel Questions forums, part of the Question Forums category; Hey forum goers, Im having a problem with my vlookup formula based on two criteria. The information is stored on ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Concatenate Vlookup

    Hey forum goers,

    Im having a problem with my vlookup formula based on two criteria. The information is stored on the data tab and the criteria are in columns A and B. ATM ive concatenated these two criteria in column C and use this as the lookup criteria. No two combinations are the same.

    On the blend tab i have validation lists (lists stored on the lists tab), with the second list being depended on what is selected in the first list. I then use the formula:
    Code:
    =IF(ISNUMBER(MATCH(BLEND!$C3&"-@-"&BLEND!$D3,INDEX(DATA!$C$2:$AR$148,0,1))),VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0),0)
    It works fine except on two combinations Bayswater1-@-12 and Bayswater1-@-14-16. I tried changing the concatenate and renaming the criteria and checking the lists etc but i cannot work out why these will not work.

    ive made a copy of the file as i thought it would be simplier to just have a look at it but i cant find the attach file option. And i have uploaded to a site hope you guys dont think its a virus or something. If there is a reputable place i can upload it that you would be comfortable downloading it from, please let me know.

    Anyway the link is http://www.mediafire.com/file/zzzmowrircz/Database.xls

    and if you have any ideas on how to fix it please let me know.

  2. #2
    New Member pdpbeethoven's Avatar
    Join Date
    Apr 2007
    Posts
    36

    Default Re: Concatenate Vlookup

    you might want to add a ' in front of all your list values to force them into text. Without spending too much time looking at this i can only guess that it has to do with cell format.

    After changing Lists!M6 to '12, it seems to make the Vlookup portion work.

    Thats a start i suppose

    Good luck.
    Last edited by pdpbeethoven; Mar 29th, 2010 at 08:07 PM.

  3. #3
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Re: Concatenate Vlookup

    Thanks for the reply, Adding ' didnt seem to make any difference for me

  4. #4
    New Member pdpbeethoven's Avatar
    Join Date
    Apr 2007
    Posts
    36

    Default Re: Concatenate Vlookup

    why are you putting the isnumber in front?

    why not just put a isna trap and set it to 0 when your vlookup comes back #NA?

    for example,

    in cell F3:

    =IF(ISNA(VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0)),0,VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0))

    This worked for me after i put the ' in front of the list items
    Last edited by pdpbeethoven; Mar 29th, 2010 at 08:43 PM.

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: Concatenate Vlookup

    1] It is better to use ISNUMBER(MATCH(…. In stead of ISNA(VOOKUP…., in avoid the double using VLOOKUP finction

    2] The formula In F3 :

    =IF(ISNUMBER(MATCH($C3&"-@-"&$D3,DATA!$C$2:$C$148,0)),VLOOKUP($C3&"-@-"&$D3,DATA!$C$2:$AS$148,43,0))

    or

    =IF(ISNUMBER(MATCH($C3&"-@-"&$D3,DATA!$C$2:$C$148,0)),VLOOKUP($C3&"-@-"&$D3,DATA!$C$2:$AS$148,43,0),"")

    Regards
    Bosco

  6. #6
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Re: Concatenate Vlookup

    Ahh i knew it be something relatively simple, made the changes and worked a treat.

    Thank you very much!

  7. #7
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Re: Concatenate Vlookup

    Bosco,

    When i enter you formula it pulls through the results for Bayswater1-@-14-16 but doesnt work for Bayswater1-@-12

  8. #8
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: Concatenate Vlookup

    Why the Bayswater1-@-12 doesn't work?

    Please refer to #2 pdpbeethoven mentioned :

    1] In Sheet Lists, cell M6, added a ' in front of 12, and became '12

    2] In Sheet BLEND, entered the formula from F3 to F12

    3] In Sheet BLEND, cell D3, re-select 12 from the dropdown list

    Regards
    Bosco

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com