Solved: Excel HLoopup

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
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
 

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 :)
 

Attachments

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
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top