CODEWITHSUNDEEP
arraysgoogle-sheets
Rick P
Rick P

Reputation: 55

How do I populate a cell based on the value in another cell in Google Sheets (advanced?)

There is probably a much simpler way of doing what I am trying to do. I'm certainly open to changing things based on suggestions.

Shared read-only file: https://docs.google.com/spreadsheets/d/1Vyfq6HxOwflIH6SYMac9UTddkIhTbTsPCzocgHCDMtg/edit?usp=sharing

Image: image of the Googlesheet

Eventual Goal: I'm trying to make a table showing the date of the highest increases for each state. columns would be: State | Daily Increase | Date of highest increase - I may use a LARGE instead of a MAX fuction but doesn't seem to make any difference for what I've tried so far.

Column O - a list of all unique dates in Column A. Column W - contains values of increases for each day (if it increased from prior days). - cell W1 is the MAX value in the aray of W5:W - trying to get the date of the highest value that matches cell W1 (in array of W5:W) and populate it in cell W2.

I'm stumped. I've tried LOOKUP for range of O5:W based on the value in W1. I've tried VLOOKUP and HLOOKUP for range of O5:W based on the value in W1. When it works without some kind of error it shows wrong date also. I've tried to figure out IF statements using arrays. I've tried INDEX, MATCH and INDEX & MATCH in the same equation. I've tried various IF statements. I've tried using ARRAYFORMULA to use just two ranges utilizing curly braces. Not sure what else to try.

Other challenges: Even if I figured out how to do it for the U.S. (column W) - If I continue to use the range O5:Y (and then O5:AA) eventually HLOOKUP or VLOOKUP will find values in earlier columns before finding the value I want for a particular state and show info wrongly.

Any ideas? - Perhaps IMPORTRANGE to a new blank sheet showing just states and max values? Then use HLOOKUP or VLOOKUP to populate the date? - Also open to using Apps Script to obtain the data and generate the info although my Apps Script skills are just slightly beyond beginner.

Upvotes: 0

Views: 947

Answers (1)

afghanimah
afghanimah

Reputation: 775

LOOKUP only works if the data is sorted, and it's not in this case.

Next best thing is VLOOKUP, but it's meant to go from left to right, so we need to use a little trick to reorganize the columns:

=VLOOKUP(W1,{W5:W,O5:O},2,FALSE)

W1 is the value we're looking for

{W5:W,O5:O} creates a virtual table where W is the first column and O is the second

2 is saying to grab the resulting lookup value from column 2 which is O

FALSE is saying that the data is not sorted

Upvotes: 2

Related Questions