Conditional Vlookup's?

[AKinner]

Hi there

I'm wanting to return a value based on a reference IF another value in a row is between a certain date range.

For example, out of all of company X's products, I want to look at the listings that were released between 1/2/13 and 1/4/13 and return the product code if those conditions are met.

Also, is it possible that if there are more than one possible entries that the first line of formula return the first relevant value, the next line of formula can return the next value and so on?

Am I trying to fit too much into this?

I'm using Excel 2010.

Thankyou
Andrew

 

[bensonby]

This is certainly possible with simple formulas.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Satisfy Criteria? (1=Yes)[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]1/1/2013[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A04[/TD]
[TD]2/7/2013[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B06[/TD]
[TD]2/8/2013[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]12/31/2012[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C07[/TD]
[TD]3/31/2013[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D10[/TD]
[TD]2/3/2013[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

You will add the 2 columns (Satisfy Criteria? and Index) to your data. (formula are easy)

Once you have the "Index", it will be straight forward for you to get a list with INDEX+MATCH.

Tips:
1. If You have a very large set of data, instead of 0,1,2,0,3,4 in the "Index" column, you'd better have 0.5, 1, 2, 2.5, 3, 4. So you can utilize binary search in the MATCH function.
2. If you just want to see the list, a simple filter on the data on "Satisfy Criteria" will do.

Hi there

I'm wanting to return a value based on a reference IF another value in a row is between a certain date range.

For example, out of all of company X's products, I want to look at the listings that were released between 1/2/13 and 1/4/13 and return the product code if those conditions are met.

Also, is it possible that if there are more than one possible entries that the first line of formula return the first relevant value, the next line of formula can return the next value and so on?

Am I trying to fit too much into this?

I'm using Excel 2010.

Thankyou
Andrew

 

[AKinner]

Thanks so much for getting back to me.

This is really good, except the document I'm referencing is unfortunately not mine to change. I need to pull out these things from it to put in a separate order form. It also needs to be dynamic based on the date entered, i.e. changing the date will return all the corresponding values within 2 months from the master sheet.

Am I entering the realm of macros?

 

[bensonby]

You can simply enter the formula in a new worksheet, even a new file.
You can also set the current date in a cell in your new file.

You won't need a macro in this case. Such simple task should not involve any VBA code.

E.g. for the date,
Cell C1 = "2/1/2013"
Then the formula of "Satisfy Criteria?" will be: =IF(and(Sheet1!B2>=C1, Sheet1!B2<date(year(C1), month(C1)+2, 1)), 1, 0)

Please feel free to let me know if you need further assistance.

Thanks so much for getting back to me.

This is really good, except the document I'm referencing is unfortunately not mine to change. I need to pull out these things from it to put in a separate order form. It also needs to be dynamic based on the date entered, i.e. changing the date will return all the corresponding values within 2 months from the master sheet.

Am I entering the realm of macros?

 

[Aladin Akyurek]

Hi there

I'm wanting to return a value based on a reference IF another value in a row is between a certain date range.

For example, out of all of company X's products, I want to look at the listings that were released between 1/2/13 and 1/4/13 and return the product code if those conditions are met.

Also, is it possible that if there are more than one possible entries that the first line of formula return the first relevant value, the next line of formula can return the next value and so on?

Am I trying to fit too much into this?

I'm using Excel 2010.

Thankyou
Andrew

A2:A100 houses the product codes, B2:B100 the release dates.

E2: 1/2/13
E3: 1/4/13

E4, control+shift+enter, not just enter:

=SUM(IF($B$2:$B$100>=E2,IF($B$2:$B$100<=E3,1)))

E6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$6:E6)<=$E$4,INDEX($A$2:$A$100,
  SMALL(IF($B$2:$B$100>=$E$2,IF($B$2:$B$100<=$E$3,
   ROW($B$2:$B$100)-ROW($B$2)+1)),ROWS($E$6:E6))),"")

 

[bensonby]

So here is another choice for you, Akinner. You will have to decide which one to use by considering your familiarity with Excel and whether the readers' familiarity with Excel formulas.

If you find the array formula difficult to understand and read, please revert to the simpler approach I have mentioned.
If you are smart with formula, you can apply the formula after understanding it.

Note: the array formula will have to be even longer because you will add worksheet names or even workbook names to it.

It's all trade-offs of readability v.s. fewer additional cells.

I tend to believe in readability with easy-to-understand formulas, because workbooks are made for human to read, not for Excel to read.

A2:A100 houses the product codes, B2:B100 the release dates.

E2: 1/2/13
E3: 1/4/13

E4, control+shift+enter, not just enter:

=SUM(IF($B$2:$B$100>=E2,IF($B$2:$B$100<=E3,1)))

E6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$6:E6)<=$E$4,INDEX($A$2:$A$100,
  SMALL(IF($B$2:$B$100>=$E$2,IF($B$2:$B$100<=$E$3,
   ROW($B$2:$B$100)-ROW($B$2)+1)),ROWS($E$6:E6))),"")