Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Help with Lookup

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello...

    I have a Excel Worksheet with some columns and one column is the number portion of an address field, another column is the text portion of the address field. For example, column 1 contains '700' and column 2 contains 'Airport dr'. This is what the worksheet looks like:
    Street# Address
    700 Airport dr

    Here comes the spinner...

    I have another worksheet which contains the text field of the address portion and it also contains a 'High' field and a 'Low' Field as well as a column which contians the city name.
    So, my second worksheet looks like this:
    Address High Low City
    Airport dr 100 300 Acworth
    Airport dr 500 800 Canton
    ...

    My question is how would I set this up so that based on the information in worksheet1 that I am able to select the second airport dr, since the street# 700, falls within the range of the second record. So, the city would be canton and NOT acworth.
    Did that make sense?

    I tried to use a VLOOKUP, but to no avail..

    All help is GREATLY appreciated!
    jags
    jagsdj@hotmail.com

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 14:47, jagsdj wrote:
    hello...

    I have a Excel Worksheet with some columns and one column is the number portion of an address field, another column is the text portion of the address field. For example, column 1 contains '700' and column 2 contains 'Airport dr'. This is what the worksheet looks like:
    Street# Address
    700 Airport dr

    Here comes the spinner...

    I have another worksheet which contains the text field of the address portion and it also contains a 'High' field and a 'Low' Field as well as a column which contians the city name.
    So, my second worksheet looks like this:
    Address High Low City
    Airport dr 100 300 Acworth
    Airport dr 500 800 Canton
    ...

    My question is how would I set this up so that based on the information in worksheet1 that I am able to select the second airport dr, since the street# 700, falls within the range of the second record. So, the city would be canton and NOT acworth.
    Did that make sense?

    I tried to use a VLOOKUP, but to no avail..

    All help is GREATLY appreciated!
    jags
    jagsdj@hotmail.com
    Use
    =INDEX(A1:D3,MATCH(700,C1:C3)+1,1)

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    I'll assume that A1:B2 in Sheet1 has the following:

    {"Street#","Address";
    700,"Airport dr"}

    and that A1:D3 in Sheet2:

    {"Address","High","Low","City";
    "Airport dr",100,300,"Acworth";
    "Airport dr",500,800,"Canton"}

    In C1 in Sheet1 enter: City

    In C2 enter:

    =IF(COUNTIF(Sheet2!A:A,B2),INDEX(Sheet2!D:D,SUMPRODUCT((B2=Sheet2!A2:A3)*(A2>=Sheet2!B2:B3)*(A2<=Sheet2!C2:C3)*(ROW(Sheet2!D2:D3)))),"Not found")

    Note. This formula assumes that for a combination of street# and address in Sheet1, there exist either no corrresponding city value or just a single city value in Sheet2. Although checking for the latter condition possible, it would be expensive to do so.

    Aladin



  4. #4
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    aladin,

    I tried your formulae and it keeps giving me "not found" for all cells. I noticed that ALL references point to Sheet2, shouldn't at least one of the references point to sheet1?

    I've tried many variations of the formulae and nothing seems to work. I would REALLY appreciate your help...thanks again.

    here is some sample data again:
    this is sheet1, SNO = street number
    SNO Street City
    2097 SUGAR PIKE RD *woodstock*
    5450 SUGAR PIKE RD *canton*

    sheet2:
    Address High Low City
    SUGAR PIKE RD 2350 2001 WOODSTOCK
    SUGAR PIKE RD 5935 2351 CANTON

    the astericks represent what SHOULD be poupulated from sheet2...

    thanks again!!
    all help is greatly appreciated


  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-20 12:28, jagsdj wrote:
    aladin,

    I tried your formulae and it keeps giving me "not found" for all cells. I noticed that ALL references point to Sheet2, shouldn't at least one of the references point to sheet1?

    I've tried many variations of the formulae and nothing seems to work. I would REALLY appreciate your help...thanks again.

    here is some sample data again:
    this is sheet1, SNO = street number
    SNO Street City
    2097 SUGAR PIKE RD *woodstock*
    5450 SUGAR PIKE RD *canton*

    sheet2:
    Address High Low City
    SUGAR PIKE RD 2350 2001 WOODSTOCK
    SUGAR PIKE RD 5935 2351 CANTON

    the astericks represent what SHOULD be poupulated from sheet2...

    thanks again!!
    all help is greatly appreciated

    Here is Sheet2 where your data is:

    Microsoft Excel - aaRetVal jagsdj.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    A1=Address

    A
    B
    C
    D
    1
    AddressHighLowCity
    2
    SUGAR PIKE RD23502001WOODSTOCK
    3
    SUGAR PIKE RD59352351CANTON
    Sheet2

    To see the formula in the cells just click on the cells hyperlink

    The above image was automatically generated by [HtmlMaker V1.26]
    If you want FREE SOFT, click here and Colo will email the file to you
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


    Here is Sheet1 where you want to retrieve the city value for a given SNO and Street:

    Microsoft Excel - aaRetVal jagsdj.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    A1=SNO

    A
    B
    C
    D
    1
    SNOStreetCity
    2
    2097SUGAR PIKE RD:alert('=IF(COUNTIF(Sheet2!A:A,B2),INDEX(Sheet2!D:D,SUMPRODUCT((B2=Sheet2!$A$2:$A$3)*(A2%3E=Sheet2!$B$2:$ B$3)*(A2%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>WOODSTOCK
    3
    5450SUGAR PIKE RD:alert('=IF(COUNTIF(Sheet2!A:A,B3),INDEX(Sheet2!D:D,SUMPRODUCT((B3=Sheet2!$A$2:$A$3)*(A3%3E=Sheet2!$B$2:$ B$3)*(A3%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>CANTON
    4
    2000KING'S RD:alert('=IF(COUNTIF(Sheet2!A:A,B4),INDEX(Sheet2!D:D,SUMPRODUCT((B4=Sheet2!$A$2:$A$3)*(A4%3E=Sheet2!$B$2:$ B$3)*(A4%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')>Not found
    Sheet1

    To see the formula in the cells just click on the cells hyperlink

    The above image was automatically generated by [HtmlMaker V1.26]
    If you want FREE SOFT, click here and Colo will email the file to you
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


    Is this not what you wanted to have?

    Aladin

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes...

    but, all its giving me is "not found" in every row. I double checked the values against sheet2 and there are matches there, but in the row there are 'not found'.

    any ideas!

    I really do appreciate your time and patience...

    thanks again...

    jags

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-20 13:03, jagsdj wrote:
    yes...

    but, all its giving me is "not found" in every row. I double checked the values against sheet2 and there are matches there, but in the row there are 'not found'.

    any ideas!

    I really do appreciate your time and patience...

    thanks again...

    jags
    Your SNO values are maybe differently formatted in Sheet1 and Sheet2. You can check this by applying:

    =ISNUMBER(an-SNO-cell)

    Another possibility is that the street mames are different because of extra spaces within an around. You can check for that bu using:

    =LEN(a-street-cell) in both sheets.


  8. #8
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    aladin,

    There's just SOMETHING i'm missing...any other ideas!?!? I just can't get it to work, I do have all the datatypes of the appropriate fields correct and they do match.

    any other ideas!
    Thanks again!!!


    jags

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the formulae:
    IF(COUNTIF(Sheet2!A:A,K2),INDEX(Sheet2!D:D,SUMPRODUCT((K2=Sheet2!$A$2:$A$4062)*(J2>=Sheet2!$B$2:$B$4062)*(J2<=Sheet2!$C$2:$C$4062)*(ROW(Sheet2!$D$2:$D$4062)))),"NOT FOUND")

    the 'sumproduct' result is returning a #NUM! error, I don't think that's right...

    Again, I really do appreciate your help and patience...

    thanks
    jags

  10. #10
    New Member
    Join Date
    May 2002
    Location
    Atlanta
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    I wanted to thank you for your help...you helped me alot...the trouble was not with your formulae but with the data types of my fields in excel!

    THanks again!
    jags

Some videos you may like

User Tag List

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
  •