• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup date from range and return cell directly to the left

J

JCTalk

Member
Hiya,

I have a requirement to lookup dates (1st Jan-31st Dec) from a table (attached), and the first instance of the date found should return the cell directly to the left of it. If I can have it search the individual date columns only then I assume that would be better, alternatively it could be the entire array. Whichever will work better I guess.

As an example, if I was searching for 17/08/2016, it would be found in cell P11 of the attached, and then return cell O11 (-1%), the cell directly to its left.

I have considered VLOOKUP and INDEX/MATCH but both on their own don't seem to have the desired effect.

I had thought maybe something using OFFSET?

It is important for this to be a formula in a cell, not VBA.

Anyone able to help with this please?

Many thanks
 

Attachments

1] Assume criteria B25 enter : 17/08/2016

2] Result C25, enter formula :

=LOOKUP(9^9,CHOOSE({1,2,3,4},0,INDEX(A2:A21,MATCH(B25,B2:B21,0)),INDEX(H2:H21,MATCH(B25,I2:I21,0)),INDEX(O2:O21,MATCH(B25,P2: P21,0))))

and,

C25 >> Custum Cell Formatting >> Type box enter : 0%;-0%;;

Regards
Bosco
 

Attachments

Hello friends,

Another one,
=INDEX(A2:21,AGGREGATE(14,6,ROW(A2:21)/(A2:21=B25),1)-1,AGGREGATE(14,6,COLUMN(A2:21)/(A2:21=B25),1)-1)

David
 
bosco_yip said:
1]
=LOOKUP(9^9,CHOOSE({1,2,3,4},0,INDEX(A2:A21,MATCH(B25,B2:B21,0)),INDEX(H2:H21,MATCH(B25,I2:I21,0)),INDEX(O2:O21,MATCH(B25,P2: P21,0))))
Click to expand...

Hi Bosco. Many thanks. This works great. Although just to check, the number of columns (in the same format as in the original workbook) will number 28. To amend, would I just add CHOOSE... (up to 29), then add in the addition index and matches? Is there a better way to handle this? Can it be done across a single array range?

Many thanks
 
bines53 said:
=INDEX(A2:21,AGGREGATE(14,6,ROW(A2:21)/(A2:21=B25),1)-1,AGGREGATE(14,6,COLUMN(A2:21)/(A2:21=B25),1)-1)
Click to expand...

Hi David. Many thanks. I tried your formula but it kept giving me a popup saying there was a problem with the formula. Do you have any special instructions for usage? Does it need to go in a specific cell etc?

Many thanks
 
JCTalk said:
Hi Bosco. Many thanks. This works great. Although just to check, the number of columns (in the same format as in the original workbook) will number 28. To amend, would I just add CHOOSE... (up to 29), then add in the addition index and matches? Is there a better way to handle this? Can it be done across a single array range?

Many thanks
Click to expand...
In your example, the duplicate dates appear always with the same value at the left,

then, try this more simpler formula :

=SUMIF(B2: P21,B25,A2:O21)/COUNTIF(B2: P21,B25)

Regards
Bosco
 

Attachments

Hi JCTalk,

AGGREGATE function,is very wide,I call it, a modern function, it has a lot of useful, I believe a Google search, you will find a lot of material.

David
 
bines53 said:
Upload a file with my formula, and see what the problem.
Click to expand...

Hi David,

I got your formula to work with the example file that we were using, but when I try to apply the same formula and logic to my actual file I keep getting a REF#! error, even though I can see the date value in the range.

Please could you take a look? I spent all yesterday staring at it to locate the problem. I'll bet it's something simple I'm missing. :-)

Many thanks David.
 

Attachments

Hi JCTalk,

Check it out, if it's okay,

=INDEX(B18:GO37,AGGREGATE(15,6,ROW(B18:GO37)-17/(B18:GO37=C41),1),AGGREGATE(14,6,COLUMN(B18:GO37)-1/(B18:GO37=C41),1)-1)

A simple rule ,
If the data is starting to row 18, you do minus 17
If the data is starting to column 2, you do minus 1

David
 
Hi JCTalk,

The latter formula is not so accurate, tomorrow I'll try to get a precise formula.

I want to understand, every eighth column, there is a date, and every eighth column has the values with the percentages, it's always like that?


David
 
Last edited:
Hi JCTalk,

The formula is longer, but accurate.

=INDEX(B18:GO37,AGGREGATE(15,6,ROW(B18:GO37)-17/(B18:GO37=C41),1),AGGREGATE(15,6,COLUMN(B18:GO37)-1/((B18:GO37=C41)*(ROW(B18:B37)-17)=AGGREGATE(15,6,ROW(B18:GO37)-17/(B18:GO37=C41),1)),1)-1)

A simple rule ,
If the data is starting to row 18, you do minus 17
If the data is starting to column 2, you do minus 1

David
 

Attachments

You must log in or register to reply here.
Back
Top