Excel two way lookup function

[slames]

I have a table with a range of numbers across the top and quantities down the side and corresponding prices in the table. I want to be able to 'look' for matching criteria on both axis and pull out the corresponding cell from within the table. Is there a way to use hlookup and vlookup together to achieve this?
Thanks
Steph

 

[hopelessliar]

Yes! although it is a little involved and if you are using it to look up values from another sheet across a network, it can be very slow...

However, here's how I do it:

Use a combination of the 'Index' and 'Match' functions.

Index('whole table range',match('valuetomatch','columnrange',),match('valuetomatch',('rowrange',))

Clear as mud isn't it!

Here's a real example using a table of data that has corners C1 and L52 where you want to find the value at the intersection between the values in A1 and B1

Index(C1:L52,match(A1,c1:c52,),match(B1,c1:l52,))

Hope this makes sense