LOOKUP with 2 values in 2 columns

users
#1

Hi,

I have a list of music numbers for our choir in calc.
Each number is identified by a number in col A, and sometimes a sub-number or letter in col B.

Someting like:

296 title1 Composer Arr. text publisher
297 a title2 C2
297 b title3 C3
298 1 title4 C1
298 2 title5 C3 A B xx
299 title6 C2

This is in the lab Mainlist.

In another tab (performed)I just want to reference number and subnumber, and get the title, composer, arranger, etc. from the Main list.

How can I lookup 2 values set in Performed E1 and F1 in the main list?

#2

Insert an extra column - I'll call it "C" - between the subnumbers and the titles. In Cn, enter
=An&Bn
This will concatenate the values in the first two columns, to give "297a", "2982", etc. You can hide that column if you prefer, so it does not need to display or print.

To harvest the title, use:
=VLOOKUP(E1&F1;Mainlist.C1:H999;2;0)
To harvest the composer:
=VLOOKUP(E1&F1;Mainlist.C1:H999;3;0)
and so on .

I trust this helps.

Brian Barker

#3

Agh, I should have thought of this myself. Simple and elegant...

Thanks Brian,

Rob.

#4

Hi Rob

Rob wrote

How can I lookup 2 values set in Performed E1 and F1 in the main list?

I believe it is not possible to lookup in two columns. The best way is to
create a combined search field in column C of the Mainlist something like
=A1&"_"&B1
On the Performed tab you can either do the same (have 3 columns) or a single
column with just the combined code e.g. 297_b and do the lookup from the
Mainlist

If someone knows a more elegant/efficient way I would also be interested in
learning.

Hope this helps.
Pedro

#5

A database would be simple and elegant. A spreadsheet lookup is a dirty
work-around at best.

#6

Agree with that!
I have no experience with base in combination with the built-in or other dbms's. I do have quite some xperience with various dbms's and SQL though. So, one of these days I'll try it..

Thanks,

Rob.