LOOKUP() Function Help

John Isenhower

I'm trying to write a function that will determine the county of an address based on the city.  So far, I have created a table with several counties forming the vertical axis and then a list of all of the cities in each county running along the horizontal axis.  It looks something like this:

 

County 1               City1              City2            City3

County 2              City4             City5           City6

County 3              City7             City8            City9

County 4              City10           City11            City12

 

My function looks like this:

 

=LOOKUP("City8", [County]1:[City]3, 1, false)

 

My understanding of the formula tells me that this function should search for City8 and then return the corresponding value in the first column of the table.  So, it should return "County 3".  However, I just get a blank cell.

 

The only time that I don't get a blank cell is when I search for the county instead of the city.  For example:

 

=LOOKUP("County 3", [County]1:[City]3, 1, false) returns "County 3"

 

Or

 

=LOOKUP("County 3", [County]1:[City]3, 3, false) returns "City8"

 

Can anyone help explain what is going wrong with this formula?  I've used the LOOKUP() function before and it works just fine in my other sheets.

 

Thanks!

Taylor F

Hello John,

 

LOOKUP will take the value that you pass it, in this case "city8", and will only look for the value in the first column of the table that you provide it. Then it will return the value that is found in the column number that you provide.

 

You will either need to move the cities into the first column and have their counties next to each city and then return the value found in the second column or a combination of MATCH and INDEX to create your own LOOKUP formula. 

 

 

=INDEX(County1:County4, IFERROR(MATCH("City 8", [City A]1:[City A]4, 0), 0) + IFERROR(MATCH("City 8", [City B]1:[City B]4, 0), 0) + IFERROR(MATCH("City 8", [City C]1:[City C]4, 0), 0))

 

 

In this formula, each city column is broken up in three parts as there are three columns for the cities. 

 

IFERROR(MATCH("City 8", [City A]1:[City A]4, 0), 0)

 

MATCH will search for "City 8" in the first city column and since it isn't present in the column it will return a #NA RESULT error. We will then use the IFERROR to return an 0 instead of the error. Then we will add the 0 to the next city column which will produce the row number where "City 8" is located. That row number will be passed to the INDEX which will return the county number in the county column. 

 

Let me know if you have any questions.

 

John Isenhower

Taylor,

 

Thank you for the response.  Your solution makes sense to me.

 

However, I simplified the table above.  In reality, I have up to 40 cities per county that I need the formula to check.  Is there an alternative that will allow me to do this more efficiently, or will I need to write out a 40 part formula?

 

Thanks!

Taylor F

If your table now displays all of the cities in the first column and your counties are in the second, you can simply repeat the counties for each city. 

 

City 1     County 1

City 2    County 1

City 3    County 1

City 4    County 2

City 5    County 2

City 6    County 2

 

Then you can use the LOOKUP function to find the County for each city. 

 

=LOOKUP('City 4", City1:County40, 2)

 

This will dispay the county for City 4 becuase it is check for City 4 in the first column of the table and is returing the value found in the second column of the table.

John Isenhower

Taylor,

 

That's what I ended up doing.  I was just looking for a more compact system because I didn't want to create that many more cells since my sheet already has 70 columns.

 

Thank you anyways for your help!

qgarrett

I'm working with a similar formula except the formula won't autofill when I type in the new row. Also, when I drag the formulas from the row above, my lookup table shifts down a row as well. I need the lookup table to stay the same but the search value should move to match the row. Is this possible?

It would be better if I could do a lookup table in a different sheet, then my dataset would be all cells.

Of note: I tested to make sure it was picking up a pattern in the rows above by coloring those cells, the new row matched the colors but not the formulas.