Possible vlookup with nested formula to find data after multiple variables checked

[KWALL15]

Okay, so I've been working with this most of the day and decided I needed help, and I'll probably be pushing my excel knowledge limits to the max over the next year so if anyone can help on this one it would be huge.


Data looks like this:
Sheet 1

a - b - c
AZ - lee county collection - blank
AZ - bar county tax agency - blank
AR - new county government - blank
AR - lee county collection - blank
NY - collection for cool county - blank
CA - excel town and county collection - blank
CA - another county collector - blank
IL - lee county - blank

Sheet 2
a -b -c
AZ - Lee county - code 38
AZ - bar county - code 45
AR - new county - code 37
AR - lee county - code 66
NY - cool county - code 55
CA - excel county - code 41
CA - another county - code 40
IL - lee county - code 42

I need a formula that will first look up a matching state and then only look at counties in that state and when a matching county is found return the code information in the next (3rd) column to column c in the first sheet. The issue is I also have to use a formula like vlookup that will allow for a "close enough" rule for the second part so that when it looks for lee county tax collector it can find just "lee county."

Also I would use text to column to get the words I need to look at to be exact but as seen above some of the words aren't in order and there are over 22,000 that I need a formula I can just drag down.

Can anyone help me out with this? I'd seriously appreciate it!!!

[FDibbins]

Hi and welcome to the forum

The issue is I also have to use a formula like vlookup that will allow for a "close enough" rule

That only applies to values, not text.

This ARRAY formula will pull out most of the answers for you. However, your data format is inconsistent...
=INDEX(Sheet2!$C$2:$C$9,MATCH(Sheet1!A2&LEFT(B2,FIND("county",B2,1)+5)&"*",Sheet2!$A$2:$A$9&Sheet2!$B$2:$B$9,0))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If you can ensure that the county and "county" are always at the beginning (unlike "collection for cool county"/cool county and excel town and county collection/excel county) then that should work for all of them