Matching two columns and returning the third?

**jarrah31** · 03-08-2023, 04:28 PM

Hello,

I have a working formula that matches a single column but I can't figure out how to also match a second one.

I've attached an example spreadsheet and included some screenshots below to hopefully help explain the problem.

Within a sheet called Lists, I have 3 defined ranges:
Screenshot 2023-03-08 at 20.00.37.png

Within the Transactions sheet, column F contains the following formula:

=IFERROR(INDEX(CATEGORY,MATCH(TRUE,ISNUMBER(FIND(DESCRIPTION,C2)),0)),"")

This takes the description in C2 and looks for a match in column A (DESCRIPTION) from Lists, returning the relevant Category from column C.
Screenshot 2023-03-08 at 20.01.16.png

At the moment I can't do a valid match when there are multiple transactions with the same name but different Amounts. I would like to do a double match against the Description and Amount that then returns the relevant Category. Would someone be able to help figure this out please?

Thanks in advance.
Example.xlsx

**Estevaoba** · 03-08-2023, 06:47 PM

Hello.

Maybe you try:

Formula:

Please Login or Register  to view this content.

This is an array formula. You might have to confirm with Ctrl+Shift+Enter, instead of just Enter.

Good luck!

**alansidman** · 03-08-2023, 06:51 PM

Power Query solution. Join the two tables on the common fields.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Load each table to the Power Query Editor and then join the two tables.

**sandy666** · 03-08-2023, 07:08 PM

I didn't check Alan's file so
another way with Power Query

Date	Type	Description	Value	Category
01/03/2023		UKI T/A DIRECTLINE	-20	me
01/03/2023		UKI T/A DIRECTLINE	-10	wife
02/03/2023		UKI T/A DIRECTLINE	-40
02/03/2023		Savings Account	-50	To Savings
02/03/2023		UKI T/A DIRECTLINE	-30	dog
03/03/2023		SAVINGS ACCOUNT	60	From Savings
04/03/2023		BRITISH GAS	-20	Energy
05/03/2023		WATER	-150	Energy

Please Login or Register  to view this content.

**jarrah31** · 03-09-2023, 04:27 PM

Thank you very much for your help Alan and Sandy, I didn't know about Power Query so I will certainly look into that to see how you kindly implemented it in both examples!

**sandy666** · 03-09-2023, 04:33 PM

You are welcome

if you have any questions just post it

**alansidman** · 03-09-2023, 07:10 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**jarrah31** · 03-11-2023, 11:19 AM

Good point, I’ll sort that out now.

Mind is so far blown by what Power Query can do! I may have a few further questions once I’ve read into it more (eg I need to find out if it can remove duplicate rows from multiple source files, ie rows containing the same date/description/amount). I’ve only just started looking into it so no need to answer that just yet, I’ll test and experiment first.

**alansidman** · 03-11-2023, 11:28 AM

Here is a link to a really good primer for Power Query

https://www.amazon.com/Master-Your-D...40186797&psc=1

**jarrah31** · 03-11-2023, 12:12 PM

hmm that's a real shame, no option for folder as a data source in the MacOS version of Excel yet. Ah well, time to fire up the Windows VM...

(thank you for the book suggestion)

**sandy666** · 03-11-2023, 12:17 PM

a few version of MacOS support Power Query, you'll need to find them

**jarrah31** · 03-30-2023, 03:56 PM

Sorry to be a pain, I unfortunately simplified things in my original example by indicating that Description cells within Lists were an exact match with the Descriptions field from my transactions. In reality the cell is a partial match, and I realised this wouldn't work after crafting the PQ myself and seeing it only match on whole cells.

Do you know if this is possible please? I experimented with fuzzy matching with no luck, and I tried the suggestion from this article, but that just multiplied my rows for some reason.

I've attached an example spreadsheet to show what I mean because I'd also like it to match transactions when the category has no associated amount if possible please.

No worries if this is asking too much, I'm probably trying to over complicate things anyway...

Example3.xlsx

**sandy666** · 03-30-2023, 04:23 PM

maybe this one

**jarrah31** · 03-31-2023, 05:12 AM

ohh, as simple as using Distinct! Thank you!

One thing I've spotted though - is it possible to add the "Food" category when no value has been specified in Categories please?
tmp_excel.png

Another issue is that I tried to change the "BRITISH GAS" Amount within Lists from -20 to -25, but after pressing Refresh All within the Data Ribbon, the Merge1 sheet retained the "me" category when I was expecting it to clear. Am I missing something?

**sandy666** · 03-31-2023, 05:23 AM

files in post#14 are not attached properly

btw. you need to change values in both tables but see post#17

Date	Type	Description	Value	Category
28/02/2023		Before UKI T/A DIRECTLINE	-20	me
01/03/2023		Text before UKI T/A DIRECTLINE and after	-10	wife
01/03/2023		Before UKI T/A DIRECTLINE	-20	me
02/03/2023		Savings Account	-50	To Savings
02/03/2023		UKI T/A DIRECTLINE	-40	extra
02/03/2023		UKI T/A DIRECTLINE After	-30	dog
03/03/2023		SAVINGS ACCOUNT	60	From Savings
04/03/2023		before BRITISH GAS after	55	Energy
05/03/2023		WATER	-150	Energy
09/03/2023		09MAR23 CD , TESCO STORES 2167,	12.45
10/03/2023		CD , TESCO STORES 1234	14.2
16/03/2023		16MAR23 , AMZNMKTPLACE, AMAZON.CO.UK	22.22
18/03/2023		18MAR23 CD , SAINSBURYS S/MKTS , LONDON	13.2

**jarrah31** · 03-31-2023, 06:10 AM

oops, now fixed, thanks.

**sandy666** · 03-31-2023, 06:15 AM

you need to declare which description is for
how is excel supposed to know which description is for food and which is something else

post#15 was edited

anyway check this one

Date	Type	Description	Value	Category
01/03/2023		Before UKI T/A DIRECTLINE	-20	wife
01/03/2023		Text before UKI T/A DIRECTLINE and after	-10	wife
02/03/2023		Savings Account	-50	To Savings
02/03/2023		UKI T/A DIRECTLINE	-40	wife
02/03/2023		UKI T/A DIRECTLINE After	-30	wife
03/03/2023		SAVINGS ACCOUNT	60	To Savings
04/03/2023		before BRITISH GAS after	-89999	Energy
05/03/2023		WATER	-150	Energy
09/03/2023		09MAR23 CD , TESCO STORES 2167,	12.45	Food
10/03/2023		CD , TESCO STORES 1234	14.2	Food
16/03/2023		16MAR23 , AMZNMKTPLACE, AMAZON.CO.UK	22.22	Amazon
18/03/2023		18MAR23 CD , SAINSBURYS S/MKTS , LONDON	13.2	Food

**jarrah31** · 03-31-2023, 08:23 AM

Ahh, it's clever how you are using an If statement to do the partial matches, and how you manipulate the Transactions first before doing a join. I really appreciate you taking the time to help with this.

There is unfortunately a problem with the resulting table. The Category column has the same "wife" category for each of the different "UKI T/A DIRECTLINE" values, whereas each one should be different depending on the matched value. Sorry about this, I can see how much manipulation you're having to implement to cater for my obscure requirements. Hopefully this isn't too much of a pain to fix.

Here's a screenshot to show what I mean:
tmp_excel2.png

**sandy666** · 03-31-2023, 08:32 AM

your screenshot is attached improperly again

**jarrah31** · 03-31-2023, 08:50 AM

I don't know what's happening there. I clicked the "Insert Image" icon, selected the image from my computer, and clicked "upload file". The resulting line had ATTACH within square brackets, a number, and then \ATTACH after the numbers.
When I went back to edit, I deleted the line and repeated the same process, with the same results, but this time the embedded image worked.
Maybe it's because my first reply was done within "Quick Reply", and the edit was within the full fat reply dialog?

**sandy666** · 03-31-2023, 09:11 AM

wrong way, use
...............goadv.png
for excel files and pictures

you need rewrite clear-cut your DescriptionKey because with existing one you have what you see

but you can already do everything yourself, you have three options so use one

**jarrah31** · 03-31-2023, 10:07 AM

ok thank you, I'll have a play.

**jarrah31** · 03-31-2023, 10:09 AM

[QUOTE=sandy666;5809661]wrong way, use
...............Attachment 824056
for excel files and pictures

Is it possible to remove the Insert Image icon from Quick Reply if it doesn't work in there?

**Glenn Kennedy** · 03-31-2023, 10:24 AM

Why not use a conventional formula:

=IFERROR(INDEX(CATEGORY,MATCH(1,(ISNUMBER(FIND(DESCRIPTION,C2)))*(AMOUNT=D2),0)),"")

copied down, or this (delete expected results first) which spills down:

=MAP(C2:C9,D2:D9,LAMBDA(x,y,IFERROR(INDEX(CATEGORY,MATCH(1,(ISNUMBER(FIND(DESCRIPTION,x)))*(AMOUNT=y),0)),"")))

**sandy666** · 03-31-2023, 12:42 PM

Is it possible to remove the Insert Image icon from Quick Reply if it doesn't work in there?

edit post and remove whatever you want

**jarrah31** · 04-01-2023, 05:07 AM

Originally Posted by Glenn Kennedy

Why not use a conventional formula:

=IFERROR(INDEX(CATEGORY,MATCH(1,(ISNUMBER(FIND(DESCRIPTION,C2)))*(AMOUNT=D2),0)),"")

copied down, or this (delete expected results first) which spills down:

=MAP(C2:C9,D2:D9,LAMBDA(x,y,IFERROR(INDEX(CATEGORY,MATCH(1,(ISNUMBER(FIND(DESCRIPTION,x)))*(AMOUNT=y),0)),"")))

Wow, that's an awesome formula, thank you very much Glenn!! It works perfectly, and I didn't know you could make it spill down the column like that! Very nice indeed.

I'll use a combination of Power Query to scan a folder for data files, and this formula to produce the categories.

Thank you very much everyone for all your help!

Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Re: Matching two columns and returning the third?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Returning a Value by Matching Two Columns of Data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions