• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Transactions table with Category and reference issue|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Transactions table with Category and reference issue|General Excel Questions & Answers|Excel Forum|My Online Training Hub

office scripts course

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Transactions table with Category an…
sp_PrintTopic sp_TopicIcon
Transactions table with Category and reference issue
Avatar
koen meijer

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 23, 2022
sp_UserOfflineSmall Offline
1
September 23, 2022 - 7:04 am
sp_Permalink sp_Print

in the attached spreadsheet I got my issue also explained.

in tab transactions, colums a, b, e & H, i get from my bank downloads.
in column F, i got the formula, this formula looks at the description (H) and also to tab categories, cell D1, D1 describes a range of words and corresponding categories, it looks at words in the description.

QUESTION
I can't get the formula to work, in transactions, column F (step 2,1 above),
the formula to regocnise keywords in the description, defined in categories. which are on tab Categories.

Working example (in tab Transactions2): in tab Transactions 2 i got it to work, but not how i want it.

What I want is
the formula in tab Transactions column F to work properly, like the formula in Trasactions 2 column K.
in the transactions tab there's only space available for TBL_Transactions1.
the table TBL_Keywords devined in tab categories

You get it? can you help me out, please?

(I got Office365 and Windows 10)

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 1
Forum Posts: 598
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
September 23, 2022 - 4:34 pm
sp_Permalink sp_Print sp_EditHistory

Hi Koen,

I used the following formula in F2 on the transactions sheet:

=LOOKUP(1,XMATCH("*"&TBL_Keywords1[Keyword]&"*",[@Description],2),TBL_Keywords1[Category])

I copied the more complete Category list from Transactions2 (and entered a few myself), so that there were more matching entries. And you'll see that I'm using structured table references, the avoiding OFFSET and INDIRECT.

Not sure how this formula translates to Dutch Excel, which I assume you are using. But if you spend the attached file it should work directly for you.

Riny

Avatar
koen meijer

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 23, 2022
sp_UserOfflineSmall Offline
3
September 23, 2022 - 9:55 pm
sp_Permalink sp_Print

Dear Riny, 

thank you so much for the quick response! and solution !
I will study your change / proposal this weekend. First impression is that it works
Thank you again

Greetings from The Netherlands

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Susan VanRiper, QSolutions Group
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Herbie Key
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Forum Stats:
Groups: 3
Forums: 24
Topics: 6526
Posts: 28575

 

Member Stats:
Guest Posters: 49
Members: 32809
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.