solving formula based on data on separate sheet

[raven_lanozo]

i have separate sheet for all of my data and i created another sheet for my calculation.

i want to ask if let say , i will put 15 in B3(covered area), the answer in A4 will be 30 AND IN B5 WILL BE 19; based on the table on separate sheet (data)




TEST1.xlsx

[VKS]

Unmerge column D and E and then use Vlookup that should help

[:) Sixthsense :)]

I hate merged cells

Remove merged cells and fill the same data to the cells.

Then in A3 cell of Comp Sheet

Please Login or Register  to view this content.

Drag the formula down to A4.

[VKS]

Hello Sixthsense, I am interested in knowing more about "3+ROW(A1)" instead of using 4 to pick up the value from the 4th column of the table.

[raven_lanozo]

Thank you sixthsense!

[:) Sixthsense :)]

@VKS,

Hm... It's just used for incrementing the output column to avoid manual input and keeping a single formula will be easier one in case of any changes instead of going through each cells for changes.

For example in this OP's case the expected output column results are 4 and 5.

So the formula should be

In A3 cell
=VLOOKUP($B$3,DATA!$A$4:$F$13,4,1)

In A4 cell
=VLOOKUP($B$3,DATA!$A$4:$F$13,5,1)

In the above case it requires two formula's for getting two different column's output. What happens if we have more formula like this... It requires more time to check each formula whether the formula's are unique or any manual change has been made.

So instead of using multiple formula's based on the requirement, making the formula single will help us in case of any change in future.

Row(A1) will get the result as 1
Row(A2) will get the result as 2

In the case case
Column(A1) will get the result as 1
Column(B1) will get the result as 2

So here the resulting columns are just a incremental series so just added row(a1) and 3 is a constant value for getting the result as 4. When it applied to next cell the row(a1) will become as row(a2) and the result will be 2 and addition of constant 3 will result as 5.

Hope I made it clear

[VKS]

@ Sixthsense…..You already have my Star on this post. I use vlookup very often in various columns and believe me till date i was typing the formula in the first row and then copy it to other columns and then manually change the column number for all the columns.... then copy and paste it all the way down in required columns……Thanks again/VKS

[:) Sixthsense :)]

You already have my Star on this post

Hm.. Not appearing in my Latest Reputation Area , Anyhow Glad it helps you

[:) Sixthsense :)]

@VKS,

Thanks for the rep which appeared now