Beyond a vlookup (97/2000)

Topic

New Reply

Vlookups are great, but what we need is a cross referenced table. Where you look both horizontal and vertical to find your matching value. Is there an Excel function to accomplish this?

Reply | Quote

Replies

Take a look at this Chip Pearson page. Look for the heading

Double Lookups

Reply | Quote

You can do this In Excel.
First, from the top panel command bar use Tools -> Options -> Calculation and then check the checkbox in the bottom right corner that says [Accept labels in formulas]
Now, in a blank sheet anywhere, create a 2-dimensional block with headings in the first row representing say Depts,
e.g. Admin, Sales, Office, Transport, DeptD, DeptE etc.
In the left-hand first column of the table place your other list
e.g. staff, postage, phones, wages, gas, electric, cars etc etc.
Fill in some numbers in the table.
Now, in any cell type a formula like
=wages Sales
and it will return the matching entry!
Other examples:
=(Admin gas)*2
=(staff Office)+(staff Admin) + (DeptD staff)

try it!

zeddy

Reply | Quote

Zeddy, thanks for the tip… now, to make it a little more useful, how would you reference the table from another sheet in the workbook. I/m thinking it would be useful to have a worksheet with several tables and the calculations done on the first page (worksheet).

Reply | Quote

Without a clear picture of what you are trying to do, it’s hard to tell, but there’s also = INDEX() if it fits what you want.

Reply | Quote