Solved: Excel HLoopup

[alter_ego1981]

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

 

[Zack Barresse]

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]

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

 

[alter_ego1981]

Fixed part of the problem regarding Team 1 not doing what its ment to, retyped Team 1 and it fixed the problem

 

[Zack Barresse]

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.