Hello,
I have a formula right now where I use index match to get a specific value on another sheet.
However, I would like to look on two sheets. Whenever I select two sheets in my formula, it does not work. Cannot figure what I am doing wrong.
When value 6 is inserted, the result should be 111 from Sheet3. However, if value 3 is inserted, the result should be 33 from Sheet2
Any help is greatly appreciated.
You can only use Match to look at one column or row.
If you want to look at two you need to use two match formulas.
=match(E3,Sheet3!F:F,0) and =match(E3,Sheet2!F:F,0)
now join them together
=If(ISNA(match(E3,Sheet2!F:F,0)), match(E3,Sheet3!F:F,0), match(E3,Sheet2!F:F,0))
Now include the Index
=If(ISNA(match(E3,Sheet2!F:F,0)), Index(Sheet3!F:F,match(E3,Sheet3!F:F,0),1), Index(Sheet2!F:F,match(E3,Sheet2!F:F,0),1))
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Well..
Technically speaking you could use one Match...
Formula:
Please Login or Register to view this content.
worksheet name : Sheet1
Cell H9 formula
Formula:Please Login or Register to view this content.
Or index + match
Formula:Please Login or Register to view this content.
Last edited by wk9128; 12-03-2021 at 10:56 PM.
Bizarre formulaOriginally Posted by wk9128
This little change could work too:
Formula:Please Login or Register to view this content.
Thanks for your replies,
What does the ROW function exactly do and why is it needed? Is it not a bit devious to put the whole rows in there?
Formula:Please Login or Register to view this content.
This formula is working good, thanks.
However, if the sheets go up to lets say 6, is it possible to do it cleaner in this formula. I do not really want insert every sheet in my formula. I can use indirect to insert the sheets in the formula
CoenH, please update your profile to show the Excel version you are using. It is clearly newer than 2017 (TEXTJOIN).
You can try this:
=IFERROR(INDEX(INDIRECT("'"&INDEX($H$3:$H$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&$H$3:$H$4&"'!F1:F10"),D8)>0,0))&"'!B1:B10"),MATCH(D8,INDIRECT("'"&INDEX($H$3:$H$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&$H$3:$H$4&"'!F1:F10"),D8)>0,0))&"'!F1:F10"),0)),"")
It is easily adjusted to cover any number of sheets.
You MAY be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, it is an array formulae.
These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
You could use the formula in this way, changing just the number of the last sheet, exemple: Sheet2:Sheet300!B1:B5Formula:Please Login or Register to view this content.
This formula is working good, thanks.
However, if the sheets go up to lets say 6, is it possible to do it cleaner in this formula. I do not really want insert every sheet in my formula. I can use indirect to insert the sheets in the formula
Formula:Please Login or Register to view this content.
In English, since I couldn't edit my last message, don't know why.
Formula:Please Login or Register to view this content.
Glenn,
Thanks for your reaction. Would it be somehow, possible to add another filter to this formula that you posted on last?
A problem that occurs, is that there may be duplicate values in col F on the sheets I am searching on (sheet2 and sheet3). There is a way to outsmart this, by adding a min/max filter.
As seen on sheet1, I am searching with value '10' in cell D8. Now it finds the first value that belongs to value 10. This is 5555. However, the result should be 55555 (sheet3).
This can be done by adding a min/max filter. Is there any way to still add this to the formula? Because of the length of this formula, I honestly don't know where to start.
Cheers,
Coen
Here's a better way... It's easy to add sheet on. It is still an array formula:
=MAX(IF(N(OFFSET(INDIRECT("'"&TRANSPOSE(H3:H5)&"'!F1:F10"),ROW($1:$20)-1,))=D8,N(OFFSET(INDIRECT("'"&TRANSPOSE(H3:H5)&"'!B1:B10"),ROW($1:$20)-1,))))
Glenn,
Thanks a lot for your effort. Perhaps I wasn't clear enough in my explanation:
On sheet1, I am searching with value '6'. Now there are two matches on Sheet3, those are 1111 and 11111 on Sheet3 col B.
This can be fixed by adding a min/max filter. See Sheet1, cell G8 and H8. I only need 1 value.
On sheet 3, the values 1010 to 1019 are present in col I. If the min/max filter is 1014/1020, only value 11111 will be filtered out, and not the wrong value 1111.
My apologies if I wasn't clear enough.
You cetainly were not clear enough. this is the first time you have mentioned column I. What is it? What is its role???
On Sheet1, in cells G8 and H8, the two values 1014 and 1020 are present. These values can be used to filter with min/max in col I on Sheet2 and Sheet3.
If the search value is 6, there are two matches on Sheet3. The matches are 1111 and 11111 (B1 and B6). The value in B6 is the correct one.
Now if we filter with 1014 min and 1020, the value 1111 (B6) is excluded, because the corresponding filter value in col I is 1010 which is lower than 1014. The only option left are the options in row 6. This will give the right value 11111 (B6). The filter value in col I is 1015, so that's in between the 1014 and 1020.
Back in an hour or so.
OK. So using a search value n, this looks at the two sheets (2 & 3) for values >=G8 and <=H8,for values in column A, where the value in column F is = n.
It remains an array formula. It can be made a bit more "human readable" by the use of Named Ranges. Can you confirm that it is working as expected and then I'll add the Named Ranges for you.
=MAX(IF(N(OFFSET(INDIRECT("'"&TRANSPOSE($H$3:$H$4)&"'!F1:F10"),ROW($1:$20)-1,))=D8,IF(N(OFFSET(INDIRECT("'"&TRANSPOSE(H3:H4)&"'!I1:I10"),ROW($1:$20)-1,))>=$G$8,IF(N(OFFSET(INDIRECT("'"&TRANSPOSE($H$3:$H$4)&"'!I1:I10"),ROW($1:$20)-1,))<=$H$8,N(OFFSET(INDIRECT("'"&TRANSPOSE($H$3:$H$4)&"'!B1:B10"),ROW($1:$20)-1,))))))
Is the number of sheets variable, or constant? If it is variable, can I move the list of sheets to another place where there is NOTHING in the cells below the list?
If it is variable, do you want a formula to compile the list of sheets dynamically?
Glenn,
Cannot thank you enough. This is exactly how it should be working.
The number of sheets are variable, and can be moved to another place.
I am curious how your formula of compiling the lists of sheets works.
Thanks again.
Out again... Back in 45 mins!!
Version 1. YOU haver to add the sheet names manually (non- .xlsm)
Four Named ranges were set up (CTRL-F3 to view/edit).
Three of them (called Search, Criterion & Result) look like variants of this:
=N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!I1:I10"),ROW(Sheet1!$1:$50)-1,))
The 4th is called Sheets and looks like:
=Sheet1!$M$2:INDEX(Sheet1!$M:$M,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},Sheet1!$M:$M),1))
The array formula then looks like this:
=MAX(IF(Search=D8,IF(Criterion>=$G$8,IF(Criterion<=$H$8,Result))))
NOTE. In EACH of the 3 Named Ranges, change the 50 to be a number BIGGER than the maximum number of rows in any one sheet. Do not go crazy and use 100,000 if you only have 100, as it may get rather slow.
Version 2 will be much the same, except that it will generate the list of sheets automatically, and willl need to be saved as an .xlsm. There is no VBA in the file, just an old inbuilt Excel 4.0 macro.
Last edited by Glenn Kennedy; 01-28-2022 at 12:52 PM.
Version 2.
Named Range List
=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
Named Range sheets changes to (This is good for up to 19 sheets of data. How many are there?):
=$M$2:INDEX($M$2:$M$20,SUMPRODUCT(--(LEN($M$2:$M$20)>0)))
In M1, copied down to M20 (array formula):
=IFERROR(INDEX(MID(List,FIND("]",List)+1,255),ROWS(M$2:M2),1),"")
I changed the name of the working sheet to Summary. Call it what you want, BUT... KEEP IT as the FIRST tab.
If/when you add new sheet(s), the formula in column M and all results will update AS SOON as you change any value, in any cell, on any sheet.
Ooops. Not only do you have to change the 50, you also gave to change these bits in red (in both versions):
=N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!I1:I10"),ROW(Sheet1!$1:$50)-1,))
I forgot about that...
Glenn,
Thank you so much, this makes my Excel file better than I had thought.
I'm impressed by the automatic update of the number of sheets.
Right now, the formula looks at EVERY sheet in my Excel file. Would it be somehow possible, to only look at the Sheets that have a number higher than 20?
So that col M only shows Sheet20, Sheet22, Sheet23 and so on?
Honestly no clue how to make a filter that filters on the last number in a word.
A low-tech solution might be to put a sub list of column M into another column, say column N.
The formula to populate column N could be: =IFERROR(INDEX(M$1:M$20,AGGREGATE(15,6,ROW(M$1:M$20)/(MID(M$1:M$20,6,99)+0>=20),ROWS(N$1:N1))),"")
The Refers to of the named range Sheet could be: =Summary!$N$1:INDEX(Summary!$N$1:$N$20,SUMPRODUCT(--(LEN(Summary!$N$1:$N$20)>0)))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks