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

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
ExcelForum.png

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

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you. I'm not use to using Search, what would be the difference?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Find is case sensitive but Search is not case sensitive.
 

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you, that is great information to know. I appreciate your time and knowledge.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
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. :cool:)

@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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. :cool:)
This made my day.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,816
Members
410,635
Latest member
phoenix7771
Top