Formula Needed check, index??

[drom]

Hi and Thanks in Advance!

Imagine I have Range("A1:A10") as follows:
A1= VVV1, A2=VVV2, A3=VVV3...

and in onte other Sheet named eg: Sheet2 a database and this database contains in Row 1 all the values VVV1,VVV2,VVV3,VVV4,VVV5...

I need a formula as short as possible (I can get the desired answer using If statements, but I am requesting your help to learn other way)

so if Sheets2.range("H1")=VVV10 then if Sum(Columns(Sheets1!H:H)>0 then my formula will give me 10, otherwise I have to check if the sum of the values of the columns containing the value VVV9 on the row 1 on sheet2 is >0.
And if so the the result will give me 9 otherwise I will check for VVV8 and then for VVV7...

Thanks!

At present the Values VVV1 to VVV10 of the Sheet2 are always in the same place, so VVV7 is in AC1, so using if statements I can get the answer, but imagine if VVV1 to VVV10 are located randonly???


Thanks!