Vlookup & Hlookup in same formula

[gregork]

I need to write a formula that looks up both the vertical and horizontal
rows in my database to return a value on my sheet. for example:

B1= Make of car e.g ford,bmw....
A2= Colour of car e.g blue,green...
B2=(cell the formula is in) Quantity in stock

So in my database I have make of cars listed vertically and then the colours
horizontally with the stock quantity listed where they intersect.

I know how to write a lookup formula when you have a specific index or
column number to specify but I don't have any idea how to do it when both
the vertical and horzontal values are variable???

Regards
gregorK

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"

 

[gregork]

Thanks guys..these links where very helpful..I have managed to write a
double lookup formula using the MATCH and OFFSET functions.......brilliant
thanks heaps

regards
gregorK