Multi-column lookup (Excel 2003 SP1)

Topic

New Reply

Screenshot moved to zip attachment because it caused horizontal scrolling. Please don’t post inages larger than 640×480 pixels. Also provided link to post (see Help 19) and edited subject to be descriptive

Hi sdckapr,

I found your post 293687 regarding selecting data through multiple columns. It is exactly what I want however it doesn’t seem to work on my PC. Do you know if there has been any change between Excel 2002 and 2003 that would affect this?

I copied the table described and keyed in your formula. As shown in the attached screen shot…it seems to have all the right components, but keeps returning #VALUE.
When i step through the formula it seems to run into problems with the ,($A$2:$A$21&$B$2:$B$21&$C$2:$C$21) part of the formula as all of those ranges return #VALUE.

Thanks for any help.

Reply | Quote

Replies

Steve is not available at the moment, so I’ll have a stab at it. As he indicated in post 293,687, the formula is an array formula. You should confirm it by pressing Ctrl+Shift+Enter, not by pressing Enter or by clicking the green check mark in the formula bar.

Reply | Quote

Thanks Hans,

That solved the problem. I had minor problems converting it to my own spreadsheet, but finally managed to get it right. I’ve been trying to replace some VB code that a manager gave me as trying to get any support for the VB is difficult. This is a very handy formula and I can see many uses for it.

Capri

Reply | Quote

If you are going to use it a lot in the same table, I would suggest that instead of using the array formula to create the “intermediate” formula. that you add the intermediate column of concatenated columns and do a conventional Vlookup.

That is:
Insert a new col D between the current C and D.
In the new D1 enter:

=A1&B1&C1

And copy (autofill) from D1 to D2:D21
Now instead of the array formula which is (now) in O1, use instead:

=VLOOKUP(K1&L1&M1,$D$2:$H$21,N1+1,0)

This method is faster and uses less memory (Array formulas are memory hogs and slow calcs down). This creates the intermediate formula once rather than everytime the array formula is used…

Steve

Reply | Quote

Thanks Steve,

I really appreciate your suggestion. The spreadsheet we are using currently has VB in to Read_tables, but the only person in our area who knows VB is the manager who created the initial formula. Several of us would like a backup method of doing the same thing in Excel just in case the manager leaves and we have no one to support the VB code. Several people can see many uses in other places for this type of formula and your solution saving memory and calculation time will come in very handy. Thanks.

Capri

Reply | Quote