How to return a value in a column when a match is found in a below array

**timwagg** · 06-13-2023, 07:31 AM

Hi All - a simple one I expect but despite trying various tutorials I can't get my INDEX/MATCH to return what I need it too, can anyone lend a hand?

Simply put I have an array of cells (for the sake of this description we'll say B2:D10) which have a variety of tasks in them. Each row in the array represents a date listed in column A (e.g. A2 = 5th June, A3, 6th June etc...although this is not important for this question) and each subsequent column is a person (and is titled in row 1 e.g. B1 = Ben, C1 - Dave etc).

What I want to do is reference another cell which has the task name in it, use that to search the array B2:B10 and then return the name of the person in row 1 who is responsible for it (as I say the date is not required).

I have done something similar using XLOOKUP to reference the date but I can't work out how to do it, pulling out something in the same column hence trying INDEX/MATCH but maybe that's the not the right method?

Cheers

**Kaper** · 06-13-2023, 08:35 AM

Both XLOOKUP and Index/Match shall do. So either try again, making sure the data you are comparing is exactly the same.
For instance "Kaper" and "Kaper " with extra space are different values for excel.
The second advice is: use a hint from a yellow banner above and attach a small sample workbook clearly marking what is an input and what is expected output (and where shall be located)

**timwagg** · 06-13-2023, 08:51 AM

Here you go, as you can see when I try and use XLOOKUP I get a #VALUE! return. My exact setup is a little more complex than this but if I can get it to turn the names in row 1 then I should be able to sort the rest.

One more thing to add, each of the tasks are unique, they only appear once in the array.

Cheers

**AliGW** · 06-13-2023, 09:22 AM

Administrative Note:

GENERIC THREAD TITLE - JUST FUNCTION NAMES

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**timwagg** · 06-13-2023, 09:48 AM

This has been changed, thanks for flagging.

**AliGW** · 06-13-2023, 09:50 AM

Thanks - much more useful.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**timwagg** · 06-13-2023, 10:05 AM

Thanks, I've uploaded the lookup example again, but with some more details/annotations. Hopefully it's clear what I am trying to achieve. Cheers

**AliGW** · 06-13-2023, 10:10 AM

Based on the sample data, this would work:

=FILTER(B1:E1,TOROW(B2:E7,1)=H2)

But is the sample data realistic???

**windknife** · 06-13-2023, 10:20 AM

Another method

=INDEX($1:$1,SUM((B2:E7=H2)*COLUMN(B1:E1)))

**timwagg** · 06-13-2023, 10:33 AM

Thanks both, I tried both methods. The first creates a VALUE! error but the second (INDEX) creates a SPILL error which suggests it's pulling something but can't display, is that right?

The data I shared is realistic I think, my application is management of a team and keeping track of what events they have to complete on which days and each event is unique i.e. only occurs once.

**AliGW** · 06-13-2023, 10:36 AM

No #VALUE error using your sample data!!!

**timwagg** · 06-13-2023, 10:38 AM

Thanks Ali - could it be affected because the array is in another worksheet? In my specific application it is calling a value from a table, and using that to search for it in the array in the other worksheet, seeing the corresponding name (in the top row) and returning that.

**AliGW** · 06-13-2023, 10:39 AM

I asked you if your sample data was realistic - obviously it is NOT.

I cannot legislate for a data set-up that I cannot see, let alone work with.

**timwagg** · 06-13-2023, 10:40 AM

Fair enough, I was trying to simplify things as much as possible but let me try and knock something up which is closer that my use case. Bear with...

**AliGW** · 06-13-2023, 10:41 AM

Thank you.

**timwagg** · 06-13-2023, 10:55 AM

Here you go, I have manged to get it to work using your method in this new example but this is using tabs in the same worksheet rather than different sheets as I couldn't figure out how to get them to ref each other after uploading them.

As you will see, I have used some other formula to collate the tasks into a single column to allow me to use XLOOKUP but it seems the method you are describing would be a cleaner way i.e. search an array and returning either the corresponding row or column (date or name).

The other variable which sometime occurs is when two tasks are assigned to the same person on the same day i.e. the cell may say Vacuum and Mop for example. To work around this when I used XLOOKUP for pulling the date I added wildcards either side of the referenced cell i.e. "*"&[CELL]&"*" but it seems sensible to not assume that this would work with your solution..
I hope this is clearer.

Cheers

**timwagg** · 06-13-2023, 10:57 AM

Example Attached

**windknife** · 06-13-2023, 11:09 AM

Your excel's version is O365?

How about attachement.

A1

Please Login or Register  to view this content.

**AliGW** · 06-13-2023, 11:12 AM

@Windknife - please share formula in your posts!

**AliGW** · 06-13-2023, 11:14 AM

So did you try this?

=FILTER('WORKSHEET 2'!C1:F1,TOROW('WORKSHEET 2'!C2:F7,1)=B2)

**windknife** · 06-13-2023, 11:16 AM

@Ali, I have added my formula in the original post. Thank you for your reminder.

**AliGW** · 06-13-2023, 11:17 AM

Thanks.

**timwagg** · 06-13-2023, 11:19 AM

Correct O365. The formula that is used to collate the cells into a single column

=LET(a,TOCOL('[NAME OF WORKSHEET]'!B16:E29,1),BYROW($O$6:$AC$187,LAMBDA(x,CONCAT(IFERROR(INDEX(a,MATCH(UNIQUE(x,1),a,0)),"")))))

This, you'll probably have guessed was kindly provided to me by a forum member.

Cheers

**AliGW** · 06-13-2023, 11:45 AM

Which forum member??? Have you been communicating privately with someone, as I don't see that suggested in this thread? If so, then please don't do that - keep everything within the thread for everyone to follow. I am assuming this is the solution you were looking for - if not, then please clarify what else you need.

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

Also, if you have not already done so, you may not be aware that you can thank 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 all those who offered help.

**timwagg** · 06-20-2023, 06:12 AM

The code I am referring to is from another thread (and another issue I was trying to solve a while back).

I've continued to try and use the solutions offered but unfortunately neither have worked and because I'm not able to/can't figure out how to share an example which references external workbooks I'm not sure how to proceed.

As you rightly say, in the example I uploaded (using different sheets rather than workbooks) I could apply the filter function and I was able to draw the right data from one worksheet into another but when trying to do the same when referencing external worksheets I can't get the same result.

Seems this should be relatively straight as like I say, I've been able to use xlookup to find a matching cell and then pull over the contents of another cell in the same row (in my example it's a date) but I can't do the same for something in the same row.

Could anyone offer any ideas of how to share a more specific example with different worksheets, maybe that will help?

Thanks in advance.

**AliGW** · 06-20-2023, 06:14 AM

Can you not put the externally referenced data onto a worksheet in the sample workbook and reference that instead?

**timwagg** · 06-20-2023, 06:18 AM

I tried that in my previous example, and managed to get it to work but when applying the same formula to ref an external workbook, sadly it didn't work. Let me upload a more specific example, give me 2 ticks. Thanks

**AliGW** · 06-20-2023, 06:20 AM

Are you trying to reference a closed workbook? If so, then we need to know, because some functions will NOT work on closed workbooks.

**timwagg** · 06-20-2023, 06:44 AM

I need it to work for whether the workbook is closed or open and different users will need to be able to see an updated version of who is doing which task and when, whether they have the 'planner' workbook open or not.

Attached is my more specific example, with all sensitive data removed.

As I mentioned before, I'm using xlookup to pull out the date a task happens on with the help of another forum member who showed me how to populate a row with all the data found in cells in the same row. While that works fine, it's not the neatest solution and I don't want to have to keep duplicating data in order to pull out what I need. I'd much prefer to search an array, pull out the corresponding date and name found in same the row/column as match is found.

I've annotated the example so hopefully this is clear.

Appreciate your patience. I'm trying to be as clear as I can with my limited knowledge.

**AliGW** · 06-20-2023, 06:49 AM

OK - so when you say it's not working, what sort of errors are you getting? This is far more complex than you suggested at the outset!

**timwagg** · 06-20-2023, 06:52 AM

Oh dear, thanks for persevering.

When I try the FILTER option you gave me I get a #N/A returned, for the INDEX example @windknife suggested I get a #SPILL!

**windknife** · 06-20-2023, 07:00 AM

Have you clean all expected data first?
Please, upload your sample file with error.

**AliGW** · 06-20-2023, 07:09 AM

OK.

NAME: =INDEX(PLANNER!$B$1:$E$1,SUMPRODUCT((PLANNER!$B$2:$E$34=C6)*COLUMN(PLANNER!$B$1:$E$1))-1)
DATE: =INDEX(PLANNER!$A$2:$A$34,SUMPRODUCT((PLANNER!$B$2:$E$34=C6)*ROW(PLANNER!$A$2:$A$34))-1)

I corrected TWO spelling mistakes on your summary sheet!!!

**timwagg** · 06-20-2023, 07:15 AM

attached. although I now get #VALUE! for both results when applying the formula to my example. The above results occur when applying to my actual data (which I can share but would rather do so without uploading for the rest of the forum to see).

**AliGW** · 06-20-2023, 07:28 AM

(which I can share but would rather do so without uploading for the rest of the forum to see).

No, you can't do that. Firstly, we keep everything public for everyone's benefit, secondly, I wil not share personal details with you, and thirdly, you don't know me from Adam, and so cannot know that I am any more trustworthy than anyone else here.

What on Earth are you doing??? You have COMPLETELY changed my formula!

This:

=INDEX(PLANNER!B1:E1,SUMPRODUCT(((PLANNER!B2:E34=OVERVIEW!C8*COLUMN(D8:F8)))))

is NOT the same as this:

=INDEX(PLANNER!$B$1:$E$1,SUMPRODUCT((PLANNER!$B$2:$E$34=C8)*COLUMN(PLANNER!$B$1:$E$1))-1)

On top of that, you have reverted to the copy with spelling mistakes. This is VERY frustrating!

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C
14	Clean Curtain
15
16	Hand Out Clothes

Sheet: OVERVIEW

**timwagg** · 06-20-2023, 07:41 AM

Apologies for any frustration caused.

I've managed to get the formula to work in the example I shared, could you be so kind to explain what the different parts of the formula are doing so I can understand what it's doing. Then I will have a little more context for how to apply in different scenarios.

**AliGW** · 06-20-2023, 07:46 AM

For the cells in row 5:

NAME: =INDEX(PLANNER!$B$1:$E$1,SUMPRODUCT((PLANNER!$B$2:$E$34=C6)*COLUMN(PLANNER!$B$1:$E$1))-1)
DATE: =INDEX(PLANNER!$A$2:$A$34,SUMPRODUCT((PLANNER!$B$2:$E$34=C6)*ROW(PLANNER!$A$2:$A$34))-1)

For the cells in row 7:

NAME: =INDEX(PLANNER!$B$1:$E$1,SUMPRODUCT((PLANNER!$B$2:$E$34=C8)*COLUMN(PLANNER!$B$1:$E$1))-1)
DATE: =INDEX(PLANNER!$A$2:$A$34,SUMPRODUCT((PLANNER!$B$2:$E$34=C8)*ROW(PLANNER!$A$2:$A$34))-1)

and so on. Don't change ANYTHING other than the lookup cell!

SUMPRODUCT((PLANNER!$B$2:$E$34=C6)*COLUMN(PLANNER!$B$1:$E$1))-1)

Find the cell in PLANNER!$B$2:$E$34 that matches the lookup cell, then match that with the cell in PLANNER!$B$1:$E$1 and return its column number, then subtract 1 from this.

Now use this number to find the matching entry (name) in PLANNER!$B$1:$E$1.

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.

Also, if you have not already done so, you may not be aware that you can thank 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 all those who offered help.

**timwagg** · 06-20-2023, 08:13 AM

Thanks - this is most helpful and when popping that into my example it works a treat.

Sadly, when applying to my actual use case (referencing other worksheets) I continue to get errors, and oddly different rows responds different errors, one gives a #REF! and the other a value response. I've checked everything in the formula and it's as you have written, and both rows are identical only thing that changes is the cell I reference to search with as you outlined.

Not sure if the below matters but for context, in my real case example...

- The row with the names in them is row 5, now row 1. My amended formula points to row 5 though.
- As you know, the reference is to an external worksheet (it doesn't work whether it's open or closed).

Only other thing that it may be, the reference cell I use for searching the table is actually a wrap of two cells in nextdoor rows e.g. A2 and A3. I am referencing A2 as it stands, would that matter? I have also tried typing in the string with "" with the search term manually rather than using a cell ref but result is still not found.

The below is the actual formula I am using with EXTERNAL WORKSHEET used to mask the file name of my other worksheet

Please Login or Register  to view this content.

R8 is the cell where the search term I want to find sits (as mentioned above is is wrapped across R8 and R9).

Cheers

**AliGW** · 06-20-2023, 08:25 AM

So you'll need this:

=INDEX('[EXTERNAL WORKSHEET.xlsx]PLANNER'!$O$5:$R$5,SUMPRODUCT(('[EXTERNAL WORKSHEET.xlsx]PLANNER'!$O$6:$R$115=R8)*COLUMN('[EXTENRAL WORKSHEET.xlsx]PLANNER
'!$O$5:$R$5))-14)

because column O is the 15th column.

**timwagg** · 06-20-2023, 09:16 AM

wonderful, thank you that sorted it. I didn't think I was going to get there so cheers again for persevering.

**AliGW** · 06-20-2023, 09:40 AM

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.

Also, if you have not already done so, you may not be aware that you can thank 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 all those who offered help.

**timwagg** · 06-20-2023, 09:42 AM

Sure does. Done and Done. Thanks again.

How to return a value in a column when a match is found in a below array

Re: Index/match/xlookup??

Re: Index/match/xlookup??

Re: Index/match/xlookup??

Re: Index/match/xlookup??

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Re: How to return a value in a column when a match is found in a below array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Help Pulling Data - Xlookup? Index & Match? VBA?

[SOLVED] Index/Match, Xlookup, Concatenate - Help Needed

Bookmarks

Bookmarks

Posting Permissions