Excel Formula: Lookup if with multiple criteria (lookup_vector)

[jorey004]

	Last Date Qty>0	1-Jan	2-Jan	3-Jan	Stocks	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	Stocks
	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Apple	3-Jan	10	15	20	120	-	-	-	-	-	-	-	400
Orange	5-Jan	-	-	-	50	-	3	-	-	-	-	-	20
Strawberry	1-Jan	30	-	-	80	-	-	-	-	-	-	-	30

Hi,

I would like to get the date from row 1:1,
When the data from row 3:3 is greater than 0. ("-" is zero uses accounting format)
B3 =LOOKUP(2,1/(C3:N3<>0),C1:N1) but this will include the "Stocks".
So must have multiple criteria C1:N1<>"Stocks"

B3, B4, B5 is the value I want to get using formula.

I have tried to use AND but lookup_vector doesn't support it.

Thanks

 

[Habtest]

Try this in B3:

=LOOKUP(2,1/(C3:N3<>0)/($C$1:$N$1<>"Stocks"),$C$1:$N$1)

 

[jorey004]

Try this in B3:

=LOOKUP(2,1/(C3:N3<>0)/($C$1:$N$1<>"Stocks"),$C$1:$N$1)

Thank you so much it works very well.

 

[Habtest]

Glad to help, thanks for the feedback

 

[jorey004]

Try this in B3:

=LOOKUP(2,1/(C3:N3<>0)/($C$1:$N$1<>"Stocks"),$C$1:$N$1)

Hi Habtest,

I've tested it on an excel spreadsheet. It works pretty well.
However, it doesn't work on google spreadsheet. It returns #N/A

Error: Did not find value '2' in lookup evaluation.

Is there another way, that also works on google spreadsheets?
Sorry if quite unrelated to excel concerns.

 

[jorey004]

Here's the error btw.
Post is related to EXCEL concerns, should I create another thread for google spreadsheet?

 

[jasonb75]

You might have to set it as an array formula (I think that the excel method of Ctrl Shift Enter works).

Rather than starting another thread, I would suggest clicking the 'Report' link at the bottom left corner of post 1 and asking a moderator to move this thread.

 

[jorey004]

Just a summary to those who have the same concerns

Excel Formula this works pretty well:
=LOOKUP(2,1/(C3:N3<>0)/($C$1:$N$1<>"Stocks"),$C$1:$N$1)

Google spreadsheet need arrayformula (CTRL+SHIFT+ENTER) to work:
=ArrayFormula(LOOKUP(2,1/(C3:N3<>0)/($C$1:$N$1<>"Stocks"),$C$1:$N$1))

Thank you so much

Habtest and jasonb75​