Match 2 cells and total two tables

**remyte** · 07-14-2021, 05:08 PM

Hello,
I need some help with another Datasheet I am working with.

There is two main formula I am looking to create.
The first one will find the matching first and last name in a different table and copy a specific cell from the row I chose.

The second one will combine data from two different tables if the first and late names are matching.

To help with clarity, I made an excel example for you with Notes attached to the cells I am looking to create those formulas.
Thank you for your time and helps.

Edit : I made a Typo in the example. Result in K5 should be green room not Aqua Room.

**Limor_OP** · 07-14-2021, 05:21 PM

Perhaps like that:

J5 -
=INDEX($R$1:$R$9,AGGREGATE(15,6,(ROW($R$5:$R$9)/($A5&$B5=$P$5:$P$9&$Q$5:$Q$9)),1))

K5 =
=INDEX($R$1:$R$22,AGGREGATE(15,6,(ROW($R$18:$R$22)/($A5&$B5=$P$18:$P$22&$Q$18:$Q$22)),1))

L5:
=SUMIFS(X:X,P:P,A5,Q:Q,B5,R:R,K5)

**remyte** · 07-14-2021, 05:38 PM

Belinda200,
Thank you so much for your help.
The two first formulas work perfectly.
The third one is working on the example, but the actual file will have those two different tables in different tabs and not on the same row. (it actually may have more than 2 table in the future, but with your help on understanding the basic formula, I am hoping to figure out how to manipulate it)

Thank you.

**Limor_OP** · 07-15-2021, 06:31 AM

Hi remyte
You can use the INDIRECT function. how many tabs are you going to have? only these two?
Each tab is added separately to the formula. the "+" is summing the information from the 2 tabs. If you have additional - you will need to add it as well.

L5:

Please Login or Register  to view this content.

**remyte** · 07-15-2021, 01:47 PM

Belinda200,
I try my best to transfer your formula to my actual file but It looks like I am forgetting something.
I think I understand the formula.
I attached the actual file so you can see exactly all the different programs and where the cell with the formula is located.
The formula is on N3 in Students Date Base

Can you help me figure out what I missed?

Merci

**Limor_OP** · 07-15-2021, 03:19 PM

Hi,
you have the "=" sign within your formula...remove it (in 2 places)

INDIRECT("'"&J$2&"'!"&"=H5:H134")

**Limor_OP** · 07-15-2021, 03:31 PM

---Deleted----

**remyte** · 07-15-2021, 03:46 PM

Beautifully solved!

Match 2 cells and total two tables

LinkBack

Thread Tools

Rate This Thread

Display

Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Re: Match 2 cells and total two tables

Thread Information

Users Browsing this Thread

Similar Threads

Match Cells and Sum between two tables

Certain words from two different cells need to match to get total

Multiply the cells to match with the total

Match several random cells to a total cell

Sequentially subtract range of cells to match new total.

[SOLVED] Cell Total if two cells match text

Pivot Tables - Calc % using Sub-Total, not Grand Total as base

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions