[Solved] VLOOKUP across multiple cells

Discuss the spreadsheet application

[Solved] VLOOKUP across multiple cells

Postby bickle » Thu Jun 05, 2008 2:32 pm

hopefully there is a simple answer there normally is.
i currently use VLOOKUP in the regular way which works fine.
i want to match the criteria in 2 different columns with the criteria in 2 different colums and return the data from a third column in the normal VLOOKUP way.

i have attached a sample spreadsheet which may clarify what i am after if the above is not clear.
i can do what i need across multiple spreadsheets but i am looking to consolidate everything into 1
by adding the extra column/search criteria.

thks in advance
Attachments
forum example.ods
(9.45 KiB) Downloaded 999 times
Last edited by bickle on Thu Jun 05, 2008 3:24 pm, edited 1 time in total.
bickle
 
Posts: 5
Joined: Sat May 24, 2008 10:46 am

Re: VLOOKUP across multiple cells

Postby keme » Thu Jun 05, 2008 3:06 pm

AFAIK, there's no lookup function that can be made to work on multiple fields, so you have to pull them together in a new column. If the table were sorted on all selection criteria, you could have used an available column (like D) and the LOOKUP() function. As it is, you need to put it left of your return value.
  • I suggest you insert a new column A, shifting all content one cell to the right.
  • In the now empty A3, enter =B3&";"&C3, and copy that down the column.
    The semicolon is inserted to resolve ambiguities, but is not necessary if the number of characters in each cell down a column will always be the same. If such a separator is needed, it should be a character that will never be entered as field data.
  • In I3 (that used to be H3) enter =VLOOKUP(G3&";"&H3;$A$3:$D$14;4;0)
  • Now hide column A so it looks tidier.
Last edited by keme on Fri Jun 06, 2008 7:52 am, edited 1 time in total.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3160
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: (solved)VLOOKUP across multiple cells

Postby bickle » Thu Jun 05, 2008 3:25 pm

magic

thks looks good

ian
bickle
 
Posts: 5
Joined: Sat May 24, 2008 10:46 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 15 guests