Complex Index/Match or Search/Find (VBA would be an option as well)

[LindenWolf]

Good afternoon. I am trying to do a lookup that is a bit more complex than I imagined. I need to basically find a word in a column to determine which value to return. The kicker is that I need to ensure that mutilple finds are a match to pull in the right value. For Example:
Headings i have are in the following order:
ColA-ColG
Fund, Function, Account Org, Pic,LOC,Account

I have a matrix that has the criteria

I need to pull in the Category field of the look up table but I have Location that has several locations in it like 201,203,204etc...each number represents a location the same with Function, Pic, Fund, and GL

In GL if it is blank it means that all GL's are possible, if there is a value then only that value is possible.

I'm happy to attach a file if someone can tell me how to do so.

Please know that I realize there has to be a better way than excel but this is currently my only option at the moment.

Thank you for any help you can provide.

 

[LindenWolf]

Thank you. I'm not use to using Search, what would be the difference?

 

[CA_Punit]

Find is case sensitive but Search is not case sensitive.

 

[LindenWolf]

Thank you, that is great information to know. I appreciate your time and knowledge.

 

[Peter_SSs]

anyway i recommend using search instead of Find

From what I can make of the OP's formula the FIND/SEARCH is looking for digits, not letters. If that is so, there is no benefit in using SEARCH as digits do not have upper/lower case. In addition, SEARCH is generally slower than FIND. So, if I am right about what is being looked for, & no wildcards are required, then I would stick to FIND. (It is also 2 less characters to type. )

@LindenWolf
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[CA_Punit]

How do you get time to review so many things.
@Peter_SSs
Have to learn so many things from you

I wish I could have the previledge of interacting with you personally someday.

Please see Column G it is searching for Account head

(It is also 2 less characters to type. )

This made my day.

 

[Peter_SSs]

How do you get time to review so many things.

? Ha, I'm retired, so there is more time now.

 

[CA_Punit]

(It is also 2 less characters to type. )

If you type "S" & Tab you will get Search
But you have to type "Fin" & Tab to get Find

 

[Peter_SSs]

If you type "S" & Tab you will get Search
But you have to type "Fin" & Tab to get Find