HLOOKUP using MATCH and INDEX

Hello everyone,

I have a row (#2) which contains dates, month by month over 5 years, and another row which contains sales forcasts. I would like to know how I can retrieve the month for which the sales forecasts start (i.e. the first month where forecast is not 0).

I have tried doing so with the following formula:

=INDEX("Dates Row",(MATCH(1,"Forecasts Row",-1))). However, I think my problem is that in order to use the -1 indicator, the data needs to be sorted (in ascending or descending order, idk). I obviously can't do that, otherwise it would mess up my table.

Any tips, help, solutions?

Thanks a lot,

Coco

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX(A1:M1,MIN(IF(A2:M2<>0,COLUMN(A2:M2))))

A1:M1 are the dates A2:M2 are the Sales forcasts.

Thank you so much Jonmo, but when I try to do it I get a #REF error. Any idea why?

Can you post the formula you used that produces that error?
It's likely the column references don't match in each part of the formula.

=INDEX('[New-International Forecasts.xlsm]Conso'!$CN$2:$FG$2,MIN(IF(INDIRECT("'[" & 'External Data'!$I$2 & "]" & 'External Data'!$I$3 & "'!" & "CN"&Y3):INDIRECT("'[" & 'External Data'!$I$2 & "]" & 'External Data'!$I$3 & "'!" & "FG"&Y3)<>0,COLUMN(INDIRECT("'[" & 'External Data'!$I$2 & "]" & 'External Data'!$I$3 & "'!" & "CN"&Y3):INDIRECT("'[" & 'External Data'!$I$2 & "]" & 'External Data'!$I$3 & "'!" & "FG"&Y3)))))


This is what I am trying to use. Sorry for the long INDIRECT formulas, but it's the best way I found to use an index number as a row number haha

Side note: for the products for which I have no forecast, it returns the first date in the file (41395, or May 1st 2013)

Actually, Tony's advice worked!

Thank you both for your help and time, it's much appreciated!

If I figure out that #REF error I'll post why

Have a good one,

coco

Originally Posted by cocostar88

Side note: for the products for which I have no forecast, it returns the first date in the file (41395, or May 1st 2013)

for that you can probably just take the final working formula, and wrap it in an Iferror
=IFERROR(..workingformula..,$CN$2)

CN2 = the first date in the range.