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

Hi Aladin,

thank you for your reply.

I don't understand what isn't clear?

I already made some suggestions in post #8 .

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)".

Care to post two real look up values: one without suburb and one with suburb?

 

[Waimea]

Re: How to use multiple vlookups for different tables

Thank you for your reply.

"City" is a named range for Cell B2, which in turn is connected to an ActiveX ComboBox linked cell.

I am trying to use multiple VLOOKUPS using IFERROR but it doesn't work.

=IFERROR(VLOOKUP(City;Data[#All];MATCH($D89&" "&F$88;Data[#Headers];0);FALSE);IFERROR(VLOOKUP(City;Data2[#All];MATCH($D89&" "&F$88;Data2[#Headers];0);FALSE);IFERROR(VLOOKUP(City;Data3[#All];MATCH($D89&" "&F88;Data3[#Headers]!;0);FALSE);"")))

The VLOOKUP are the same for suburb and without suburb.

But I guess they would be:

=IFERROR(VLOOKUP(City;Data[#All];MATCH(F$88&" "&$D90;Data[#Headers];0);FALSE);"")

=IFERROR(VLOOKUP(City2 (subrub);Data2[#All];MATCH(F$88&" "&$D90;Data2[#Headers];0);FALSE);"")

=IFERROR(VLOOKUP(City3 (subrub);Data3[#All];MATCH(F$88&" "&$D90;Data3[#Headers];0);FALSE);"")

=IFERROR(VLOOKUP(City4 (subrub);Data4[#All];MATCH(F$88&" "&$D90;Data4[#Headers];0);FALSE);"")

Perhaps I could use multiple IF statements?

=IF(City="* (suburb);VLOOKUP1, VLOOKUP2 ?

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

I don't think this reply answers my question.

 

[Waimea]

Re: How to use multiple vlookups for different tables

Hi Aladin,

I don't understand what you want to see? These are the lookup values that I am using.

I have already shown the values that I lookup as you suggested in post 8#.

[B]Name       Population       [/B]
City          50 000
City2        60 000
City3        40 000

[B]Name       Population       [/B]
City3 (Suburb 1)  20 000
City3 (Suburb 2)  20 000

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

You seem to like abstractions... To my knowledge, there is no city which is called City3 in the world or anything like City3 (Suburb 1). Just because you think real names are irrelevant for the solution you have in mind, you still don't provide real look up values. That said, another abstract solution (my 3rd now)...

IFERROR(LOOKUP(REPT("z",255),CHOOSE({1,2},VLOOKUP(X2,TABLE,2,0),VLOOKUP(X2&"*",TABLE,2,0))),"not found")

where TABLE is a 2-column range which contains cities and/or city-suburb pairs along with the population numbers. X2 houses a city or a city/suburb pair.

 

[Waimea]

Re: How to use multiple vlookups for different tables

Hi Aladin,

I got it to work with your last suggestion, thank you very much for your time!

 

[Aladin Akyurek]

Re: How to use multiple vlookups for different tables

Hi Aladin,

I got it to work with your last suggestion, thank you very much for your time!

Glad to help.