Ask Your Question
0

VLOOKUP on multiple ranges with tilde?

asked 2019-03-20 12:41:02 +0200

DonD gravatar image

updated 2019-03-20 12:41:58 +0200

Running Libroffice Calc v6.2.0.3 on Ubuntu 18.10.

I am trying to use VLOOKUP() with multiple ranges but can't get this to work.

Following is an example of the sort of thing I am trying to do:

Sample data

The formula in cell H3 would be: =VLOOKUP( G3, (A3:B6~D3:E6), 2, 0 )

ie: Lookup the value in G3 from both A3:B6 and D3:E6 and return the second column in the matching row.

When I edit the formula both the source ranges are hilighted (see below) and everything appears correct, but I always get an Err:504

image description

Have I missed something obvious or am I trying to do something that can't be done with VLOOKUP()?

(And sorry, no... can't just move the second range to below the first. :-) )

Any ideas how I can get this to work?

edit retag flag offensive close merge delete

Comments

Yes, that is impossible (yet? Please file an enhancement request - but that would be incompatible change).

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-20 12:47:01 +0200 )edit
1

Please don't file an RFE, it won't get implemented, because it's incompatible in the sense that no other spreadsheet implements it that way and the function is defined to take one range.

erAck gravatar imageerAck ( 2019-03-20 15:07:41 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2019-03-21 11:52:04 +0200

pierre-yves samyn gravatar image

Hi

VLOOKUP on multiple ranges with tilde is not possible, but INDEX allows it and it has an argument to work with it.

Unfortunately the MATCH function does not offer this possibility. In the attached example I proceed in two steps: find range & line with MATCH and then the result you are looking for with INDEX.

Regards

edit flag offensive delete link more
0

answered 2019-03-21 20:45:55 +0200

gregors15 gravatar image

Using the same columns you used, try this.... =IFNA(IFERROR(INDEX(B3:B6,MATCH(G3,A3:A6,0),1),INDEX(E3:E6,MATCH(G3,D3:D6,0),1)),"Not Found") Regards

edit flag offensive delete link more
0

answered 2019-03-20 15:08:46 +0200

erAck gravatar image

So, short answer: No. This is not possible.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-20 12:41:02 +0200

Seen: 103 times

Last updated: Mar 21