How to automatically match cell info from one sheet to another if it matches a cell in she

[Cosmic2024]

Hi everyone,

I've just been given this forum to use if i need help with excel. I'm very basic at excel but am trying to improve. Any help i receive is greatly appreciated.

I don't know excel well. I have tried searching online on how to match cells from one cell on one sheet to that in another cell.

I've seen in another spread sheet this formula =SUMPRODUCT(--('INVOICES FULL BKDOWN'!$A$4:$A$144=$B$19),--('INVOICES FULL BKDOWN'!$B$4:$B$144=$B$24),--('INVOICES FULL BKDOWN'!$C$4:$C$144=$C$24),'INVOICES FULL BKDOWN'!$F$4:$F$144)

I have tried some of the suggestions on google for product If & index , match, i just can't get it to work.

In the spreadsheet attached i have added this in for what i'd like to achieve.


Test 1 from AG4:AG203 ( AG4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
12345dcvt3 from Ah4:Ah203 ( Ah4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
08/04/2024 from Ai4:Ai203 ( Ai4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
zero from Aj4:Aj203 ( Aj4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
01/01/2027 from Ak4:Ak203 ( Ak4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
Flowers from AL4:AL203 ( AL4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
daffodil from AM4:AM203 ( AM4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
03/03/2024 from AN4:AN203 ( AN4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
Zero from AO4:AO203 ( AO4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
16/07/2029 from AP4:AP203 ( AP4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203

I'd be very grateful if anyone can help point me in the right direction on what to use.

Many thanks in advance

[AliGW]

In the workbook, in your mock up. please show ONLY what you want to see in each cell (not a description, which we have above already).

[Cosmic2024]

Morning, thank you for your feedback. I wasn't sure which way was best.

[AliGW]

Clear all expected reults, then this in A11:

=LET(c,OFFSET('Current-Drivers'!A5:E32,,MATCH(A3,TRIM('Current-Drivers'!$A$2:$DA$2),0)-1),IF(c=0,"",c))

[Cosmic2024]

Morning ALIGW, thank you so much. this is really helpful. I'll have a play around with it.

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 each of those who offered help.

[Cosmic2024]

Morning AliGW,

Wow, your code =LET(c,OFFSET('Current-Drivers'!A5:E32,,MATCH(A3,TRIM('Current-Drivers'!$A$2:$DA$2),0)-1),IF(c=0,"",c)) works lovely for bringing in the info in rows A5 to E32 in current drivers for A2. & F, K, P, U, Z, AE, AJ, AO, AT, AY, BD, BI, BN, BS, BX, CC, CH, CM, CR, CW

But I also need the same for Rows A38:E65, A71: E99, A104:E133 & A137:E164 when a matching name is input into A3 on sheet 1 & including columns F, K, P, U, Z, AE, AJ, AO, AT, AY, BD, BI, BN, BS, BX, CC, CH, CM, CR, CW & the group of columns in between.

and match names if any of the names from these columns & rows are in current drivers are input into A3 on sheet 1

Columns A, F, K, P, U, Z, AE, AJ, AO, AT, AY, BD, BI, BN, BS, BX, CC, CH, CM, CR, CW
Rows 2, 35, 68, 101, 134



When I've changed your formula to =LET(c,OFFSET('Current-Drivers'!A5:E137,,MATCH(A3,TRIM('Current-Drivers'!$A$2:$DA$2),0)-1),IF(c=0,"",c)), it shows spill or NA if i change $A$2:$DA$2 to 134) How can I get it to pull the other rows in. I've tried =A3=TRIM('Current-Drivers'!A2,A35,A68,A101,a134) where it brings up false or true but its showing too many arguments.

I hope I make sense.


I really appreciate your help :-)

[AliGW]

See post #5 telling you how to sign off the thread.

[AliGW]

If you want further help, then you will need to provide an updated sample workbook that shows the problem.

If there was more to this than you mentioned, then you really should have said so at the outset - it's not a great idea to drip feed the requirements. Is there anything else you haven't mentioned?

[Cosmic2024]

Sorry, but I thought I had put all the requirements in my original post for info from All Clients Incl Contacts to go into Sheet 1

Test 1 from AG4:AG203 ( AG4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
12345dcvt3 from Ah4:Ah203 ( Ah4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
08/04/2024 from Ai4:Ai203 ( Ai4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
zero from Aj4:Aj203 ( Aj4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
01/01/2027 from Ak4:Ak203 ( Ak4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
Flowers from AL4:AL203 ( AL4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
daffodil from AM4:AM203 ( AM4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
03/03/2024 from AN4:AN203 ( AN4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
Zero from AO4:AO203 ( AO4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203
16/07/2029 from AP4:AP203 ( AP4) on all clients incl contacts tab if sheet1A3 matches name in D4:D203

[AliGW]

See if this does what you want:

=LET(c,OFFSET('Current-Drivers'!A5:E203,,MATCH(A3,TRIM('Current-Drivers'!$A$2:$DA$2),0)-1),FILTER(c,(INDEX(c,,1)<>0)*ISNUMBER(INDEX(c,,3))*(INDEX(c,,3)<>0)))

As I pointed out in post #2, your initial description was not terribly clear and you really didn't illustrate it that well - there are NO drivers in the extra groups, so the data isn't realistic in that sense.

You will need to test this carefully.

If it does NOT do what you need, then provide a more realistic sample set - mock up the desired results MANUALLY in the results grid.

[Cosmic2024]

HI AliGW, I'm sorry I haven't been very clear, I'm better off talking / screen sharing than I am at trying to put it in writing.

As you've suggested, I've done a more realistic mock up with example tabs. The notes are in Sheet 1, hopefully it makes more sense.Complete List Mock Up V1.xlsx

[Cosmic2024]

Good Morning

I don't know excel well. I have tried searching online on how to match cells from one cell on one sheet to that in another cell.

I've seen in another spread sheet this formula =SUMPRODUCT(--('INVOICES FULL BKDOWN'!$A$4:$A$144=$B$19),--('INVOICES FULL BKDOWN'!$B$4:$B$144=$B$24),--('INVOICES FULL BKDOWN'!$C$4:$C$144=$C$24),'INVOICES FULL BKDOWN'!$F$4:$F$144) but I know this won't work as it's for sum, rather than matching cells.

I have tried some of the suggestions on google for product - If & index , match, I just can't get it to work.

I've attached a mock up spread sheet with notes in for what I'm trying to achieve, with some example tabs

I'm grateful for any help please, many thanks in advance

[JeteMc]

Doesn't seem as if it should be this hard to get the driver's name, however here is a formula that returns the expected result:

=INDEX(All_Clients_INCL_Contacts!AG4:AG73,AGGREGATE(15,6,(ROW(All_Clients_INCL_Contacts!AG4:AG73)-ROW(All_Clients_INCL_Contacts!AG3))/(All_Clients_INCL_Contacts!F4:F73=LEFT(A3,SEARCH(" ",A3)-1)+0)/(All_Clients_INCL_Contacts!E4:E73=RIGHT(A3,LEN(A3)-SEARCH(" ",A3))),1))

EDIT: Found out why INDEX MATCH didn't work.
The formula in column D of the All_Clients_INCL_Contacts put two spaces between the value in column F and the text in column E
1. Modify the formula in column D to read: =F4&" "&E4
2. Use the following to get the driver's name: =INDEX(All_Clients_INCL_Contacts!AG4:AG73,MATCH(A3,All_Clients_INCL_Contacts!D4:D73,0))
Let us know if you have any questions.

[Cosmic2024]

Morning JeteMc

Thank you for your help & suggesting the above. I will have a play around in my mock up & will get back to you. Once I know it's all working I'll update the thread to solved.

I appreciate your help :-)

[Cosmic2024]

Hi JeteMc

I've been testing your suggestion and it works lovely.

[JeteMc]

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.