Multiple vlookups for different tables

[Waimea]

Hi, I am trying to use several tables in an if formula but I haven't gotten it to work.

I have one master table which I want to look up to first, however, if the lookup value contains City (Suburb), City2(Suburb) or City3 (Suburb) I want to look up from three different lookups tables.

I have 4 Tables in total, master, table1, table2, table3.

Can anyone help me with this?

Thank you in advance!

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

Not clear. Try to provide an example look up value and the names of the relevant tables along with an indication where (in which table or tables) the look up value might occur.

 

[Waimea]

Re: How to use multiple vlookups for different tables

Hi Aladin,

thank you for your reply. I apologize for not being clear.

In the master table, I have 290 cities and all cities are text values. I am using an activex combobox that has 320 values.

In table1 I have 10 cities where all cities have a (suburb), in table2 I have 5 cities were all cities have (suburb) and in table3 I have 14 cities where all cities have a (suburb).

I am thinking that perhaps I could split the strings in the vlookup at the "(" in "(Suburb).

Master Table:
City1
City2
City3

etc

Table1:
City3 (Suburb 1)
City3 (Suburb 2)
City3 (Suburb 3)
City3 (Suburb 4)
City3 (Suburb 5)

etc

Table2:
City10 (Suburb 1)
City10 (Suburb 2)
City10 (Suburb 3)
City10 (Suburb 4)
City10 (Suburb 5)

etc

Table3:
City12 (Suburb 1)
City12 (Suburb 2)
City12 (Suburb 3)
City12 (Suburb 4)
City12 (Suburb 5)

etc

An example lookup would be:

=IFERROR(VLOOKUP(ActiveXCombobox;MasterTable[#All];MATCH($D92&" "&F$91;MasterTable[#Headers];0);FALSE);"")

Another example would be:

=IFERROR(VLOOKUP(ActiveXComboboxWithSuburb;Table1[#All];MATCH($D92&" "&F$91;Table1[#Headers];0);FALSE);"")

where suburb has 8 characters before the "(".

 

[Waimea]

Re: How to use multiple vlookups for different tables

Any suggestions? I would be really grateful for all help I can get with this.

 

[Waimea]

Re: How to use multiple vlookups for different tables

I am still working on this without success, I need someone to help me to create a formula to look up from 4 different data tables.

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

The issue is not clear. Are we looking up a city? If so, does that city occurs only in one of the 4 tables? When the city is found, what are we returning as result, a suburb?

You can describe the task without reference to any formula by just having an example look up value, a table in which this value occurs, and what result from that table must be returned. You could post 5 rows from such a table along with a look up value that occurs in that table.

 

[Waimea]

Re: How to use multiple vlookups for different tables

Hi Aladin,

thank you for your reply.

I have 4 data tables set up as above, I want to use 4 different VLOOKUP tables depending on the lookup value.

If City1 to 290 I want to use Master Table.

If City 3 (Suburb) to City 3 (Suburb 10) I want to use Table1.

If City 10 (Suburb1) to City 10 (Suburb5) I want to use Table2.

If City 14 (Suburb) I want to use Table3.

I am trying to lookup a combo box with a total of 320 cities in them. I am returning the number of people in that city.

Master Table:
Name Population.
City1 550 000
City2 500 000


Table1:
Name Population.
City3 (Suburb 1) 850 000
City3 (Subrub 2) 700 000

etc.

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

The conditions you specify for which table to use is not crystal clear...


VLOOKUP(city,mastertable,2,0) should return the population related to city.


VLOOKUP(X2,INDIRECT(VLOOKUP(X2 or part of X2,tablelist,2,0),2,0) should return the population related to X2, which houses a look up value like City3 (Suburb 2).

 

[Waimea]

Re: How to use multiple vlookups for different tables

Hi Aladin,

thank you for your reply.

I don't understand what isn't clear? I want to lookup 320 values, some of the values have "(suburbs)" in them, I want to use another lookup table based on the presence of "(suburb)".

Is it possible to use IF and VLOOKUP and MATCH?

 

[Waimea]

Re: How to use multiple vlookups for different tables

I found this thread https://www.mrexcel.com/forum/excel-questions/603509-multiple-vlookups-one-formula.html and also https://exceljet.net/formula/multiple-chained-vlookups. The latter link doesn't seem to return N/A when I am using VLOOKUP and MATCH functions together.

I want all my vlookups to be in one formula, I guess I could use two tables, one for cities without suburbs and one table for cities with Suburbs??