1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel HLoopup

Discussion in 'Business Applications' started by alter_ego1981, Oct 5, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. alter_ego1981

    alter_ego1981 Thread Starter

    Joined:
    Jun 26, 2008
    Messages:
    49
    Hi All,

    Hope you can help, its been a while since i have done a lookup and my brain has decided to give up.

    Attached is a rough sample of what i am working on.

    On Purchased by tab, i want to show which team brought the property at the earliest time and to show what team name it is, ie team 1, team 2 etc etc

    Hope you can help
     

    Attached Files:

  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    I'm sure there are many ways to do this, but try something like this in C3...

    =INDEX('Data Entry'!$B:$K,1,MATCH('Purchased By'!B3,INDIRECT("'Data Entry'!B"&D3&":K"&D3),0))

    You'll get an #N/A! error if no value is found. If you need to negate this please let us know. And I used this in D3...

    =MATCH('Purchased By'!A3,'Data Entry'!$A:$A,0)

    Copy down as needed. You can shorten your MIN formula to the following...

    =MIN('Data Entry'!B3:J3)

    It will inherently ignore text.

    HTH
     
  3. alter_ego1981

    alter_ego1981 Thread Starter

    Joined:
    Jun 26, 2008
    Messages:
    49
    Thanks,

    that has helped so much, however i need to run if the person had brought the site.

    updated spreadsheet.

    Also could you have a look at Purchased By and Rent and D34 : D62 some of them should be blank but for some reason its saying "Pay Rent" the code works for the other teams except Team 1

    Thanks :)
     

    Attached Files:

  4. alter_ego1981

    alter_ego1981 Thread Starter

    Joined:
    Jun 26, 2008
    Messages:
    49
    Fixed part of the problem regarding Team 1 not doing what its ment to, retyped Team 1 and it fixed the problem
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Some of your values have trailing spaces, i.e. D33 on the "Purchased By and Rent" worksheet. The value is "Team 1 " where it should be "Team 1". Do you see the difference? See the trailing space? The values are NOT the same, hence you're getting skewed results. That's why the formulas will change when you re-type the value, because you didn't re-type it with a trailing space. If you want to get rid of them easily, download/install www.asap-utilities.com and it has a nifty tool where you select the cells in question, run the menu item to delete leading/trailing spaces from all selected cells. Very handy tool.

    In C2 of the same sheet you'll need to change your formula. You need a CSE formula, or a Ctrl + Shift + Enter formula, otherwise known as an array formula. This formula is entered one cell at a time and confirmed with not just Enter, but Ctrl + Shift + Enter. Array formulas are very powerful. You'll need to do something first though, you'll notice on the data entry sheet where Team 2 has a YES but no time. This will need to be changed. Take out the YES or enter a time, otherwise a zero value will be returned and it will read 12:00:00. The formula to enter is...

    =MIN(IF('Data Entry'!$C3:$U3="YES",'Data Entry'!$B3:$T3))

    When you confirm with CSE Excel will put squiggly brackets around it { and }. Note that you DO NOT manually enter these squigglies, but you will know that you entered it right if you see them.

    Also not as part of the formula you'll need to enter "YES" instead of "Yes" If you want to enter any value or case sensitivity you'll need to change the formula to ...

    =MIN(IF(UPPER('Data Entry'!$C3:$U3)="YES",'Data Entry'!$B3:$T3))

    And remember, if you entered it correctly it will look like this ...

    {=MIN(IF(UPPER('Data Entry'!$C3:$U3)="YES",'Data Entry'!$B3:$T3))}

    DO NOT MANUALLY ENTER THE BRACKETS!

    The formula in D can remian the same.

    Let us know how this works for you.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/756462

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice