LinkBack URL
About LinkBacks
IF(countif($A$1:$A5,A1)=1,B1) but IF(countif($A$1:$A5,A3)=2,C1)
I tried,
{=IF(countif($A$1:$A5,A3)={1;2;3;4;5},{B1;C1;D1;E1;F1})} but doesnt seem to work?
IF(countif($A$1:$A5,A1)=1,B1) but IF(countif($A$1:$A5,A3)=2,C1)
I tried,
{=IF(countif($A$1:$A5,A3)={1;2;3;4;5},{B1;C1;D1;E1;F1})} but doesnt seem to work?
Last edited by Dwexdwex; 08-17-2013 at 04:08 AM.
Hi!
You don't need an array formula here at all, just try this:Let me know if this is what you were afterFormula:
Please Login or Register to view this content.
Thanks for the reply, ajryan88.
Actually I'm trying to avoid that because if there a lot cell reference to choose from e.g IF(countif($A$1:$A20,A15)=10,B10..
I will be need to create a huge nested formula which will properly exceed the nested limit.
Last edited by Dwexdwex; 08-17-2013 at 03:04 AM.
{=IF(countif($A$1:$A5,A3)={1;2;3;4;5},{B1;B2;B3;B4;B5,0})}
It looks to me the syntax of your if formula is incomplete.
Try the formula above and please reply.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Please explain what you are trying to do?
If possible please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Apologies Dwexdwex, maybe you could try something a little left-field:P.S. I'm not at my computer at the moment, but I think this formula should do what you want. If it doesn't work then I must have missed a parameter in the function, so let me know and I'll try again when I'm next at my computer.Formula:Please Login or Register to view this content.
This will need to be entered as an array formula, not a regular formula.
Hope this helps
Last edited by ajryan88; 08-17-2013 at 03:33 AM.
Hi, oeldere. Don't seem to be working still but thanks.
Example.xlsx
Instead of all the value1 retrieving outcome1.
Desired result: 1st "value1" retrieves outcome1, 2nd "value1" retrieves outcome2, 3rd "value1" retrieves outcome3 so on..
@Dwexdwex:my solution will give you the outcome you desire based on your previous post
Hi ajryan88, it sure does but i'm exploring the possibility of a bigger selection which is not practical with a multiple nested IF function I feel and it is definitely beneficial to learn a neater alternative solution for this.
No sorry about the confusion, I meant my second suggestion, using the INDIRECT function
@ Dwexdwex,
Thanks for the attachment file... Please give some description about your data and what you are trying to do with it and where and based on which for giving the exact solution.
Oh yeah, sorry I think I caused a bit of confusion on my side :X , supposed to be the change of columns not rows. eg. {=IF(countif($A$1:$A5,A3)={1;2;3;4;5},{B1;C1;D1;E1;F1})} instead of {B1;B2;B3;B4;B5;B6}.No sorry about the confusion, I meant my second suggestion, using the INDIRECT function
outcome 1 , 2 , 3 are the data retrieved from "looking up" with lookup value as C2&D2.
Desired result is to tag it back to it's lookup value (C2&D2) but because there are duplications, I do not want the 2nd duplication to retrieve value of "outcome1" but instead.. "outcome2" and so on..
Hi again,
Please consider the formula in cell B2 (which has then been copied down column B, rather than inserting an array formula).
Please let me know if this has been any more helpful than any of my previous posts/suggestions :P
Wow! worked out pretty good! R1C1 cell ref, very cool. Definitely an addition to my arsenal now thanks to you ajryan!
Thanks 6thsense and oeldere for looking up my query.
Definitely a useful thing to remember. You're very welcome!
Please don't also forget to mark this thread as solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks