=IFERROR(INDEX($K$1:$AA$1,MATCH("Z",K3:AA3)),"YTS")
In the above Index-Match function, I wanted to Index a value from K1:AA1 by Matching and finding the final text value from K3:AA3. However, I want to exclude cells T3:V3.
If I use the following, I get result only upto K3:S3, ie., it does not include the result from W3:AA3. Please suggest a solution.
=IFERROR(INDEX($K$1:$S$1,MATCH("Z",K3:S3)),INDEX($W$1:$AA$1,MATCH("z",W3:AA3),"YTS"))
File attached for review.
Last edited by AliGW; 11-15-2017 at 02:04 AM. Reason: To explain my issue properly
Explain it well what is your expected result and what is your thought how can it done?
Last edited by AliGW; 11-15-2017 at 02:05 AM. Reason: OP has amended title.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Try this:
=IFERROR(INDEX($A$1:$AA$1,MAX(LOOKUP(2,1/(K3:S3<>""),COLUMN(K:S)),LOOKUP(2,1/(W3:AA3<>""),COLUMN(W:AA))),"YTS")
Last edited by AliGW; 11-15-2017 at 02:25 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Dear Mr. Shukla,
Thanks for the suggestion. I have updated the Title now. I need to get the answer at cell F3 which should be "Imp/IA" instead of "Trg" because in the formula (given below), I have selected range K1:S1 & W1:AA1 and excluded T3:V3. In the formula below, I am getting result beyond S3. Please suggest.
=IFERROR(INDEX($K$1:$S$1,MATCH("Z",K3:S3)),INDEX($W$1:$AA$1,MATCH("z",W3:AA3),"YTS"))
Hope I am clear now.
See post #3.
Superb Ma'am, I liked it very much very easily handle the arguments but parentheses which closed in second lookup and lookup array wrongly.Originally Posted by AliGW
=IFERROR(INDEX($A$1:$AA$1,MAX(LOOKUP(2,1/(K3:S3<>""),COLUMN(K:S)),LOOKUP(2,1/(W3:AA3<>"")),COLUMN(W:AA))),"YTS")
It should be as below.
Formula:
Please Login or Register to view this content.
Well spotted - now amended.
No, both solutions do not resolve my query. Please check it again by deleting my entries from K3 to Q3. This results in "YTS" which is wrong because U3 is has an entry, eg. "Fin". This means that your solution does not take count beyond S3. Please suggest new solution again.
ok it can refine with iferror formula
Give try
Formula:Please Login or Register to view this content.
I may have interpreted incorrectly.
Try array entering this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Dave
Thanks a lot Ms. Ali, Mr. Shukla and Mr. Flame. Finally, I got the answer. Thank you all again...
This is shorter that my previous. Still array entered.Formula:Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Sorry, I noted two more errors as follows:
1. I get "0" (zero) instead of "YTS" if I delete all entries from K3:AA3 when I use the following formula. Please note "YTS" is given at the end of the formula.
2. Please note that I use Dates in alternate columns, for eg., L3, N3, etc. When I enter a date, this formula does not work, ie., the result at F3 become "0". Therefore, this formula works only when I remove all dates from cells. To know yourself, please enter any date at N3 in the Excel sheet attached in my first post.
=IFERROR(INDEX($A$1:$AA$1,MAX(IFERROR(LOOKUP(2,1/(K3:S3<>""),COLUMN(K:S)),0),IFERROR(LOOKUP(2,1/(W3:AA3<>""),COLUMN(W:AA)),0))),"YTS")
I provided a solution based on the sample data you gave. In future, please provide REALISTIC sample data. I am afraid I do not have time to look at this now, as I am busy.
Dear Ms. Ali,
Sorry for not giving an complete Excel sample earlier. Now, attached the sheet with the formula given by you and also added the dates in the respective cells. As of now, the answer is "0" at F3. Once we remove all dates in the row, the result will be as I wish. Sorry again. Please make corrections.
As I said above, I am not able to help at the moment - sorry.
Ok... No problem. You can provide the solution when you are at leisure. However, I want to suggest you one more point that the formula may yield correct result if it 'look' or 'match' the 'maximum' 'text' value in the row.
Thank you - that's very kind of you.
A propos, you seem to have an idea of how to make this work yourself - did you try tweaking my suggested formula? If so, how did you get on?
This is a long-winded thread. Please explain what you want in F3 and WHY the expected result is zero. It's hard to tell what you want, just by looking at a fromula that does not provide the desired result. A few rows of data WITH A CLEAR EXPLANATION would be MUCH more helpful than just one row.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Dear AliGW,
Yes, I tried tweaking your suggestion and found that there seems to be two things need change.
1. If there is no entry in the range K3:AA3, then the formula should yield "YTS" which condition is set at the end of the formula.
2. In the above range K3:AA3, when we enter dates in the alternate cells like L3, N3, etc., we get the result at F3 as "0" which is wrong. I found that my earlier suggestion to you about finding the maximum "Text" value is not correct because your formula accepts even dates. However, I found that the formula accepts date until P3 and the required result being arrived at F3. But, when we add date at R3, F3 become "0". I think the Lookup for MAX value in the formula looks up to K3:S3 only, not beyond.
Your formula appended below for your review again and I have attached the worksheet again with dates added up to R3. If we remove the date from R3, our preferred result arrives.
=IFERROR(INDEX($A$1:$AA$1,MAX(IFERROR(LOOKUP(2,1/(K3:S3<>""),COLUMN(K:S)),0),IFERROR(LOOKUP(2,1/(W3:AA3<>""),COLUMN(W:AA)),0))),"YTS")
Thank you again for your interest in taking up my issue.
Dear Glenn,
My expected result from the attached worksheet is:
I have a range K3:AA3 wherein if I fill in the cells with a status and its completed date, the formula should find the maximum position of the entry in the range and Index a corresponding value from the range K1:AA1 and place the result at F3. For example, If the last entry in K3:AA3 is at Q3, then the formula should Index result from Q1 (corresponding Cell above the Max position) at F3. However importantly, I would like the formula to exclude the range T3:V3 from Lookup/Match.
And, if there is no entry in any of the cells in the range K3:AA3, then the result at F3 should be YTS.
I have attached the worksheet for your review.
Try
Formula:Please Login or Register to view this content.
Dear Mr. Shukla,
Yes, it works fine but a small update is needed that when there are no values in the range K3:AA3, then the result should be YTS in F3. This condition is already laid in your formula, but does not work now. Please update.
Try
Formula:Please Login or Register to view this content.
Dear Mr. Shukla,
Thanks a lot. This works exactly... However, I wonder as to why when a simple INDEX/MATCH formula (given below) can resolve this issue, just excluding T3:V3 needs a complicated formula (since I am novice) like yours and AliGW's. Could you please explain this. Then, I will close this thread "Solved". I just wanted to learn.
=IFERROR(INDEX($K$1:$AA$1,MATCH("Z",K3:AA3)),"YTS")
You've answered this question yourself - it's because you want to exclude a range. Excel cannot guess what you want it to do unless you tell it. The solutions you have been offered require slightly longer formulae to allow for that exclusion, but are not in themselves particularly complicated.
Thanks a lot Ms. AliGW and Mr. Shukla for your excellent support...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks