[Closed] Excell HELP!! formula is driving me batty

vlookup cant look left.
only right

=INDEX(B:B,MATCH(MAX(C:C),C:C,0),1)

Edit: If you have two dates with the same number it will only pick up the first one. Don't know if that's a problem.

And you're dumping a lot of people. Playaaaa!

Does the LARGE function help?

Actually, the offset of 10 - what's that meant to be returning? You probably want to copy the dates into another column and hide it. As it stands, you're looking for the value 23 in the range B2->B9. It doesn't exist there - hence the #N/A

Just copy the dates (or create a column with =B2 etc) into a new column between C and D (it it's the new D).

Then use VLOOKUP(max(C2:C9), C2:D9, 2, false)

Edit - or use index/match as above 🙂

cheers! having a crack at that....

Edit: Sneaky edit by the OP 🙂

=B7

HTH

Can you write me an example code that would work please?

it depends.

as above - are you able to change the layout of the sheet?

if not, then you cant use vlookup and have to use OFFSET/INDE etc or similar.

If you can change the layout, either move the date column to the right of the VALUES column and use the formula posted above, OR, add a second date column one to the right of the VALUES column and use the VLOOKUP formula on those two columns (Now C & D)

That code returns a value (not a date)
And the value does not actually exist anywhere in the spreadsheet?

Is this because the date values need to be formatted in a specific way eg. date/text etc

[img] [/img]

A date is just a value - you need to format the cell as a date and you'll get your date.

Format that cell as a date.

Each date is actually a number, with 01/01/1900 being 1, 02/01/1900 being 2. etc. So 41553 is 06/10/2013.

The correct value is 3255 in cell C185 which should display the date 06/10/2013

Actually... in your match() statement, the last argument should probably be 0 not 1. Your data isn't sorted and you want an exact match (from memory, please check)

A date value is the number of days since 1900 and something.. not related to the 3255 value - not sure what that is. If you format the cell as a date (can just use the dropdown currently saying "General"), you should see the date.

[img] [/img]

Actually... in your match() statement, the last argument should probably be 0 not 1. Your data isn't sorted and you want an exact match (from memory, please check)

The match statement ends in zero already. The 1 is for the column number of the INDEX range (B:B). It might be unnecessary because there is only one column in the range.

Legends Thank You!!

The match statement ends in zero already. The 1 is for the column number of the INDEX range (B:B). It might be unnecessary because there is only one column in the range.

So it does.. as you were (sorry bails!)

So it does.. as you were (sorry bails!)

For anyone interested, INDEX MATCH does the same thing as a VLOOKUP, but in two seperate steps.

So if you find a match to your lookup text in the tenth row of your MATCH range, then you can return the tenth row of your INDEX (the date, in this case) range. But because each bit is built seperately you can offset the two (e.g add a +1 afetr the closing bracket of the MATCH and you'll return the row below the one that matches. Or you can specifiy A1:A10 as the MATCH range and B21:B30 as the INDEX range.

Most of the time it's just useful for a doing a straightforward VLOOKUP to a column on the right, like this.