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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Thank you, that is great information to know. I appreciate your time and knowledge.
 
Upvote 0
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’)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top