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?

 

[crazydragon84]

you can you index match.

=index(Your data range in sheet2,match(Lookup value,Sheet2!A:A,0),match(Date Value,Sheet2!1:1,0))

 

[NotaVBAeXpert]

Thanks.. Now if I wanted to show the first value in this row, what would be the proper way to adapt the formula?

 

[Aladin Akyurek]

What is expected if successful - a number or text?

 

[NotaVBAeXpert]

number.

 

[Aladin Akyurek]

number.

I assume that this formula

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

​

is located in Non-Std Calc and Sheet5 is actually Std Values. A further assumption that A30 might occur more
than once in 'Std Values'!C:C.​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>
If so, then:

=INDEX('Std Values'!$C$2:$CG$1500,MATCH(1,IF('Std Values'!$C$2:$C$1500=A30,
    IF(ISNUMBER(1/INDEX('Std Values'!$C$2:$CG$1500,0,MATCH(G30,'Std Values'!$C$1:$CG$1,0))),1)),0),
    MATCH(G30,'Std Values'!$C$1:$CG$1,0))

​

 

[NotaVBAeXpert]

I assume that this formula

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

​

is located in Non-Std Calc and Sheet5 is actually Std Values. A further assumption that A30 might occur more
than once in 'Std Values'!C:C.​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>
If so, then:

=INDEX('Std Values'!$C$2:$CG$1500,MATCH(1,IF('Std Values'!$C$2:$C$1500=A30,
    IF(ISNUMBER(1/INDEX('Std Values'!$C$2:$CG$1500,0,MATCH(G30,'Std Values'!$C$1:$CG$1,0))),1)),0),
    MATCH(G30,'Std Values'!$C$1:$CG$1,0))

​

When I update this code, the output is showing "N/A".. What exactly are we saying to excel in this code? Thank you!

 

[Aladin Akyurek]

When I update this code, the output is showing "N/A".. What exactly are we saying to excel in this code? Thank you!

Are the assumptions I made correct?

 

[NotaVBAeXpert]

When I update this code, the output is showing "N/A".. What exactly are we saying to excel in this code? Thank you!

Sheet5 is the Std Values and the Non-Std Calc is the sheet with the formulas in them. The values in A30 on the Non-STD calc will not repeat.

 

[Aladin Akyurek]

Sheet5 is the Std Values and the Non-Std Calc is the sheet with the formulas in them.

So far so good.

The values in A30 on the Non-STD calc will not repeat.

Does A30 repeats in Std Values?