Find values in a table greather than zero in a dynamic range of a table

wbasket

New Member
Joined
Apr 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello All,

i have a table like a following one (of course, mine is bigger) that is coming from a query:

Countries01/01/202001/02/202001/03/202001/04/202001/05/2020
Argentina00300025000
Australia01000015003000
Austria15000003000
Belgium00020000
France10000004000
Italy03000000


In the same file i have i have a cell, let's say B2. In B2 i'll insert all the time a different date.

What i'd like to do is to now, is obtain a new table where i'll have, for every single row (country):
  • First value>0 starting from the date greater or equal to the one in B2
  • The date (column header) where the value >0 has been found

Example:
If B2= 01/03/2020
my table will be:

CountryValue (>0 for dates >=B2)Date (only dates>= B2)
Argentina300001/03/2020
Australia150001/04/2020
Austria300001/05/2020
Belgium200001/04/2020
France400001/05/2020
Italyblank cause nothing>0 has been found for B2>=0blank cause nothing>0 has been found for B2>=0

For the VALUE column i was thinking to apply the following:
  • IF formula to check if the date in the column header is >=B2, and if this is true then check if the value is >0. IF both conditions are true then provide me the value. IF one of the conditions is false check the next column header/value.
For the DATE column i was thinking to apply the same as before but with a different order:
  • check at first if a vale in the row is >0 and if this is true check if the date (column header) is >=B2. If both conditions provide me the column header, otherwise proceed with the next cell and repeat the operation
For both operations i don't know how to set up a formula that will repeat the operation to next cell in case one or both conditions will be false. Does anybody knows how to do it?

Anyway, this would be my approach. All the solutions to reach a layout like the second table will be really appreciated.

Thank you in advance for your help!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top