0

I have a list of around 20 items and the user can pick three of them to perform a comparison, so I need a formula which gets the "TRUE" and gives me the contents of the correspondent cell in the same row. For example;

A29 = "True" then I should get the contents of cell E29 which is London, need this up to three occurrences to be able to complete a comparison.

How can I do this?

3
  • Have tried a offset but seems that I can't work it, so I got stuck
    – vitordf
    Jul 30, 2013 at 11:50
  • Can you clarify how your data is laid out? A29 and E29 already have content, so what cell are you placing the function in?
    – CharlieRB
    Jul 30, 2013 at 11:57
  • What about a vlookup or data validation with a macro? Jul 30, 2013 at 12:10

3 Answers 3

1
=IF(AND(A29=TRUE;B29=TRUE;C29=TRUE);E29; "")

This will return London when the A, B and C field are TRUE and an empty string when one of them is FALSE.

Another example for multiple values:

Input:

London    TRUE
Lisabon   FALSE
Brussels  FALSE
Madrid    TRUE
Moscow    FALSE
Stockholm TRUE

You could use this formula to get the capitals that have a TRUE value

=CONCATENATE(IF(B1=TRUE;CONCATENATE(A1;",");""); IF(B2=TRUE;CONCATENATE(A2;",");""); IF(B3=TRUE;CONCATENATE(A3;",");""); IF(B4=TRUE;CONCATENATE(A4;",");""); IF(B5=TRUE;CONCATENATE(A5;",");""); IF(B6=TRUE;CONCATENATE(A6;",");""))

Result:

London,Madrid,Stockholm,

Keep in mind that I'm using a Belgian Excel. It uses ; instead of , as a separator in functions.

2
  • Thanks, but maybe I didn't express myself correctly. I have a list of around 20 items and the user can pick three of them to perform a comparison, so I need a formula which get's the "TRUE" and gives me the contents of the correspondent cell in the same row.
    – vitordf
    Jul 30, 2013 at 11:54
  • I have added another example. Is that what you need? Jul 30, 2013 at 12:32
0

If I understand your question, you need to use an IF function.

IF(logical_test, [value_if_true], [value_if_false])

For instance you could use something like the following;

IF(A29="TRUE", E29, "")

This will check cell A29 to see if the text TRUE exists. If it does, it will return the contents of E29, otherwise it will do nothing.

You can use nested IFs, also the AND and OR functions for multiple criteria depending on your needs. As your question is written, it is unclear exactly how you need it.

Reference

2
  • Thanks guys think I have got it =IFERROR(INDEX($E$3:$E$20,SMALL(IF($A$3:$A$20=TRUE,ROW($A$3:$A$20)-ROW($A$3)+1),ROWS($A$3:A3))),""). Where the first input is from A3 to A20 and second column input E3 to E20 results are gathered within range F3 to F5 as only 3 occurrences are required. Thanks to everyone who have helped.
    – vitordf
    Jul 30, 2013 at 13:14
  • 1
    @vitordf if this answer or another answer solved the problem, please mark it with the green checkmark. If not, you can submit your own answer and mark it with the checkmark. Doing so allows users to know that this question has been answered Jul 30, 2013 at 13:33
0

For this example, I'm going to put True/False in A1:A20, and your list in B1:B20

In C1, to find the first choice, I use =MATCH(TRUE,$A$1:$A$20,0) and to get the city name, in D1, I use =INDEX($B$1:$B$20,SUM($C$1:C1))

In C2, I had to alter the formula slightly, as I need to search for the next match, skipping what had been found previously: =MATCH(TRUE,OFFSET($A$1,SUM($C$1:$C1),0,20-SUM($C$1:$C1),1),0)
D2 is actually D1 copied down, which Excel changed the formula to =INDEX($B$1:$B$20,SUM($C$1:C2))

C3 and D3 are C2 and D2 copied down (this can actually be repeated if you decide to give them more choices later)

To join the (up to 3) together, I used =D1&(IF(ISERROR(D2),"",","&D2&(IF(ISERROR(D3),"",","&D3))))

Example in action:
Example

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .