Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Concatenate v & (Excel 2003)

    Hi

    I am trying to shorten this formula using & instead of concatenate, but I just can't seem to get it right.

    =IF(CONCATENATE(F3,G3)="",E3,VLOOKUP(CONCATENATE(F 3,G3),Matrix,4,FALSE))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate v & (Excel 2003)

    <pre>=IF((F3&G3)="",E3,VLOOKUP((F3&G3),Matrix,4,FA LSE))
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    HI Legare

    Thanks for that, it was the double brackets where I had the trouble.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Braddy, you say its the double brackets where you went wrong! This formula also works! The brackets do not play a role around the concatenated references!
    =IF(F3&G3="",E3,VLOOKUP(F3&G3,Matrix,4,FALSE))
    Regards,
    Rudi

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Hi Rudi

    Thanks for your suggestion, as you see from my origional formula I put brackets round (F3&G3) in error.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Hi Rudi

    I am trying to add ISNA to your formula with no success I think it's brackets again

    =IF(ISNA(F3&G3="",E3,VLOOKUP(F3&G3,Matrix,4,FALSE) ),,(F3&G3="",E3,VLOOKUP(F3&G3,Matrix,4,FALSE))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Hi Braddy,

    I'm assuming you want a msg to display if the Vlookup returns no match. In that case try : =IF(F3&G3="",E3,IF(ISNA(VLOOKUP(F3&G3,Matrix,4,FAL SE)),"No Match",VLOOKUP(F3&G3,Matrix,4,FALSE)))

    You had the ISNA in the incorrect position! Let me know if I'm barking up the wrong tree though!
    Regards,
    Rudi

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Hi Rudi

    Thanks for the response I am actually looking for a zero, I realise I can acheive the this by removing "No Match" and leaving ,,

    My grateful thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    The ,, will not be good!

    For a 0 use ,0,
    For a blank cell use ,"", (open and close a double quote!)
    Regards,
    Rudi

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Hi Rudi

    Thanks for that.

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Concatenate v & (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Using "", results in a null string. It is not a "blank cell". You can not have blank/empty cell as the result of a formula, since the cell has something in it (the formula).

    Steve

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate v & (Excel 2003)

    Sorry... its just the terminology I used...I suppose I could have been a little more accurate!... Thx!
    Regards,
    Rudi

Posting Permissions

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