Finding 1st value in row if prior cells in the row are blank

[NotaVBAeXpert]

Hi,

I am trying to find the amount located in specific cell based on month/year(x/xx). I have multiple worksheets; the first worksheet showing what value to look up and the specific date, for example... I need to lookup value 454839 on 1/13(January 2013).

My 2nd worksheet shows the data with the lookup values going down Column A and the dates across the columns in the 1st row.

I currently have a vlookup and match function:

(=VLOOKUP(A30,'Std Values'!C:CG,MATCH('Non-Std Calc '!G30,Sheet5!$C$1:$CG$1,0),FALSE)

But I need help figuring out a macro or formula if the output from my above formual is blank.because there is a value in those cells. I would need the next value in that particualr row which would match the value in the row.Any ideas?

 

[NotaVBAeXpert]

So far so good.



Does A30 repeats in Std Values?

A30 would not repeat.

 

[Aladin Akyurek]

A30 would not repeat.

Then I don't understand what this remark of yours mean: "

But I need help figuring out a macro or formula if the output from my above formual is blank.because there is a value in those cells. I would need the next value in that particualr row which would match the value in the row."​

 

[NotaVBAeXpert]

Then I don't understand what this remark of yours mean: "

But I need help figuring out a macro or formula if the output from my above formual is blank.because there is a value in those cells. I would need the next value in that particualr row which would match the value in the row."​

Just to clarify,

I have two worksheets, Sheet 1 and Sheet 2.

Sheet 1 will show my formulas and starting value for my project.

Sheet 2 includes the core data in which the top row shows the dates (i.e. 1/11 through 1/17) and the first column has the reference number which is also on sheet 1.

I currently have a formula which is using the index and the match function to match the date for the specific reference column.

=INDEX('Sheet2'!A:CG,MATCH($A64,'Sheet2'!$C:$C,0),MATCH('Sheet1 '!$G64,'Sheet2'!$1:$1,0)*MATCH('Sheet1 '!$G64,'Sheet2'!$1:$1,-1))

Now if there isn't a populated cell in in the initial formula output, I would then need the next value in that particular row.

The reference column in Sheet 1/Sheet 2 do not repeat.

 

[Aladin Akyurek]

Let me clarify...

Does $A64 occur more than once in 'Sheet2'!$C:$C?

If one occurrence has a corresponding blank in the return range, then it's possible to go the occurrence which has a corresponding non-blank (numeric) value in the return range.​

 

[NotaVBAeXpert]

Let me clarify...

Does $A64 occur more than once in 'Sheet2'!$C:$C?

If one occurrence has a corresponding blank in the return range, then it's possible to go the occurrence which has a corresponding non-blank (numeric) value in the return range.​

Ok, It would occur more than once on Sheet2.

 

[Aladin Akyurek]

Ok, It would occur more than once on Sheet2.

This should work. Control+shift+enter in B64 of Sheet1, not just enter:

=INDEX(Sheet2!$C$2:$CG$1500,MATCH(1,IF(Sheet2!$C$2:$C$1500=$A64,
    IF(ISNUMBER(1/INDEX(Sheet2!$C$2:$CG$1500,0,MATCH($G64,Sheet2!$C$1:$CG$1,0))),1)),0),
    MATCH($G64,Sheet2!$C$1:$CG$1,0))
​

​

 

[NotaVBAeXpert]

This should work. Control+shift+enter in B64 of Sheet1, not just enter:

=INDEX(Sheet2!$C$2:$CG$1500,MATCH(1,IF(Sheet2!$C$2:$C$1500=$A64,
    IF(ISNUMBER(1/INDEX(Sheet2!$C$2:$CG$1500,0,MATCH($G64,Sheet2!$C$1:$CG$1,0))),1)),0),
    MATCH($G64,Sheet2!$C$1:$CG$1,0))
​

​

Would there be a reason why I am getting an "N/A" Error?

 

[Aladin Akyurek]

Would there be a reason why I am getting an "N/A" Error?

Select the MATCH bit in the formula bar and hit F9. If this gives #N/A, this could mean: either G64 is no available in Sheet2!$C$1:$CG$1 or the date in the latter is text, not true date.

 

[NotaVBAeXpert]

Select the MATCH bit in the formula bar and hit F9. If this gives #N/A, this could mean: either G64 is no available in Sheet2!$C$1:$CG$1 or the date in the latter is text, not true date.

I verified the dates are actual dates as well G64 is present on sheet2.

 

[Aladin Akyurek]

I verified the dates are actual dates as well G64 is present on sheet2.

And that bit does not return #N/A?

If you changed the formula in any way, let's how it looks.