Excel 2007 : Vlookup -New Excel 2007 User

[zitu708]

Hey i am a new Excel 2007 user.
I am using VLOOKUP function as follows-
VLOOKUP(A3,'History'!$B2:$E2000,2,FALSE)

Here I want to replace value "2" in "$B2" dynamically such that the new value which is available in a different cell can replace it. Suppose cell P22 has value 46, so dynamically my function becomes
VLOOKUP(A3,'History'!$B46:$E2000,2,FALSE)


Thanks.

[ratcat]

G'day zitu and welcome to the forum,

Try this

=VLOOKUP(A3,INDIRECT("'History'!$B"&P22&":$E2000"),2,FALSE)

Cheers

RC

[zitu708]

Thanks a lot, but following did not work and returned error message #REF!

=VLOOKUP($A2,INDIRECT("'History'!$B"&I22&":$AA$2000"),2,FALSE)

Can you please look into this again?

Regards.

[NBVC]

Are you sure the reference sheet name is History?

Excel does not allow that name for a sheet... it says it is a reserved name!

If you have spaces etc, you have to include in the formula where you reference the sheetname.

[zitu708]

Yes you are right as Excel does not permit History as sheet name and I had sheet name as Hist ory . I have now changed the sheet name as MyHistory, but unfortunately it is still not working, May be I am still making some silly mistakes.

=VLOOKUP($A2,INDIRECT("'MyHistory'!$B"&I22&":$AA$2000"),2,FALSE)


Regards.

[NBVC]

Do you have a positive value in I22? If it is 0 or blank, you will get the #REF! error

[zitu708]

There is probably no Prize for my accepting that I was making silly mistakes.

Value generated in cell I22 was certainly negative.

Now this function is working absolutely fine.

Many many thanks.

Regards