How to lookup a value in a calc table using multiple keys?

[AntonelloLobianco]

Let’s say I have a table in Calc that I want to use as source like this:

key1	key2	value
a	1	0.1
a	2	0.2
b	1	0.3
b	2	0.4
c	1	0.5
c	2	0.6

And then I want lookup values, in another table in the same spreadsheet file, using key1 and key2:

ky1	key2	value
b	2	0.4	← I need these values
a	1	0.1	← I need these values

How I do obtain 0.4 and 0.1 ? Wih a single criteria I would use VLOOKUP. I have understood that I should use the INDEX and MATCH functions, but MATCH itself works only on a single criteria…

[erAck]

Insert a key column with concatenated key1,key2 (formula =B2&","&C2 copied down) like

key	key1	key2	value
a,1	a	1	0.1
a,2	a	2	0.2
b,1	b	1	0.3
b,2	b	2	0.4
c,1	c	1	0.5
c,2	c	2	0.6

and then use VLOOKUP() or MATCH() with similar concatenated keys.

[AntonelloLobianco]

I can’t add a column but this unexpectately works:
=INDEX(value_col_source,MATCH(CONCATENATE(key1_dest,key2_dest),CONCATENATE(keys1_source_range,keys2_source_range)))

In particular what I didn’t know is that the second CONCATENATE, having two vectors as input, returns also a vector, that the MATCH function can use to lookup the specific value requested.

Thank you!

[PKG]

ask789602.ods (11,3 KB)

[AntonelloLobianco]

Thank you, I didn’t know about the & operator. It basically does a vector concatenation (at least in this case).

[sokol92]

You can also try the method suggested by Microsoft.

[Villeroy]

» Using multiple criteria in Excel LOOKUP formulas«
LOOKUP is the one and only lookup function which is completely inadequate for this job.

[erAck]

@AntonelloLobianco

I can’t add a column but this unexpectately works:
=INDEX(value_col_source,MATCH(CONCATENATE(key1_dest,key2_dest),CONCATENATE(keys1_source_range,keys2_source_range)))

In particular what I didn’t know is that the second CONCATENATE, having two vectors as input, returns also a vector

And for that the expression is very inefficient because for every MATCH() call it needs to create that (even identical) array. It doesn’t matter for some dozens or hundreds values, but may for some thousands.