PDA

View Full Version : lookup or match



av8tordude
01-28-2012, 11:45 AM
I would like to Look up or match the City/State from column Q & R with the City/State in column T & U and take the County associated with the City/State in column V and and insert the county into column S.

If no match is found for the City/ State, then leave the County for the City/State blank. Some Cities do not have States. If a City does not have a State, leave County blank.


Can someone assist, Thank you

mdmackillop
01-28-2012, 04:38 PM
If you want a macro free solution, you could combine each pair of columns in "helper" columns and use Lookup, otherwise you need VBA. What is your preference?

av8tordude
01-28-2012, 05:12 PM
I updated Book1 to assist in understanding what I'm trying to achieve. I would like to update the Counties (yellow column) for all City/State in the USA. If no match is found for the City/ State, then leave the County for that City/State blank.

VBA function/Code would be the preferred method. Thanks

shrivallabha
01-28-2012, 10:25 PM
Try.

In Cell S11:

=IF($R11="","",INDEX($T$12:$V$533,LOOKUP(99999,SEARCH($R11&$Q11,$T$12:$T$533&$U$12:$U$533,1),ROW($V$12:$V$533)-ROW($V$11)),3))

And copied down!

parttime_guy
01-29-2012, 10:37 PM
Hi av8tordude, shrivallabha

Both have same results... the file attached has the vlookup formula

Happy Excelling!!! :friends:

Best regards