Look up function best place

P

pianolady

I would like to be able to look up a cell from Column A and come up with a
response in Colum B many to one and one to many relationships
i.e.

Apples Granny Smith
Golden Delc
Honey Crisp

etc
another relationship is

Granny Smith = Apples

Most of my people are comfortable in excel , how do i do a lookup for
something like that.
 
B

Biff

This is relatively easy if you setup the raw data table like a database:

Instead of this:

Apples........Granny Smith
....................Golden Delc
....................Honey Crisp
Cars............Chevy
.....................Ford
....................Chrysler

Set it up like this:

Apples........Granny Smith
Apples........Golden Delc
Apples........Honey Crisp
Cars............Chevy
Cars............Ford
Cars...........Chrysler

Otherwise, it would be *extremely complicated* if it can be done at all
(although I didn't try it but I'm pretty good with lookups)

Here's a sample file:

2-way lookup.xls 15.5kb
http://cjoint.com/?kwbFTJLjeu

The formulas are array formulas:

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff
 
Top