Excel Formula Problem

[marcus101]

Hi:

I need to write a formula that looks up multiple (ie: more than two) columns in a named data range.

The trick is, I need to select specific rows where I know the fixed values of three to five of the columns.

I know INDEX/MATCH can handle two column values when trying to locate specific information.

Say I have these columns:

Street City Province Postal
Bank Ottawa ON K2P 1L3
Bronson Ottawa ON K1C 5L3
Carling Ottawa ON K2P 0Z9
Thomas Toronto ON M4C 5B2
Severn Thompson MB L3M 2B3

I want to search for values in the Street, City and Province. Any way INDEX/MATCH can handle that?

I've tried using the Lookup add-in, with no luck.

Thanks,



marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[xlbo]

If you are doing what I think you are doing, concatenate Street, City & Province and do the lookup on that..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

or you culd use the DGET function

This springs to mind coz I see Dale's watching this thread!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[GComyn]

I've done this before... what I do is have another column that is the concatenation of all of the columns that I'm looking up, then look for that string.

Example

Col1 col2 col3 col4 Col5
ABC A B C Data

Then I just use the vlookup function to look up ABC...