combining vlookup and hlookup

[vishal]

hi,

I have tried using vlookup and hlookup seperately. However, I need t
use them together to get a certain data.

currently i have a spreadsheet in which i have names of stocks fro
column c2 to i2. I also have dates from rows b3 to b10. This creates
matrix of 8x8 with name of the stock on top of the matrix and dates o
the left of the matrix.

My problem is , I have another sheet which has stock name in cell B
and date in A6. I need the cell B6 to return me the stock price whic
is in the matrix after looking up both the stock name and the date.

Is this possible? Please let me know.

Thanks,
Visha

 

[Jason Morin]

Try:

=INDEX(Sheet1!B2:I10,MATCH(Sheet2!B5,Sheet1!B2:I2,0),MATCH
(Sheet2!B6,Sheet1!B2:B10,0))

where Sheet1 contains the matrix and Sheet2 contains your
stock and date criteria.

HTH
Jason
Atlanta, GA

 

[vishal]

Hi Jason,

I have tried using the funciton you mentioned. However, I am gettin
totally different results. Please see the attached file and tell m
where am i going wrong

Attachment filename: combining vlookup and hlookup.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56625