Looking for formula with VLOOKUP and IF

[franck.so]

Good evening,

I need some help with a more complex formula than I usually setup.
I have a list of paper (documents) sent by authors who have all a Number assigned.
Each document have several authors name. Some have a value "TRUE" and others "FALSE"
On "sheet 1" I would like to search a document number and display the corresponding author marked as "TRUE" in sheet 2.
I assume this should be done with a VLOOKUP and IF fonction mixed but I can not find out.

I attache the excel worksheet for better overview.

Thanks for your help!

Franck

[Tony Valko]

Try this array formula**:

=IFERROR(INDEX(Data!B2:B20,MATCH(1,(Data!A2:A20=A2)*Data!D2:D20,0))&" "&INDEX(Data!C2:C20,MATCH(1,(Data!A2:A20=A2)*Data!D2:D20,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FlameRetired]

I interpreted a little differently using this formula in B2 of Result sheet ..... array-entered.

Please Login or Register  to view this content.

and filled down.

If it helps file attached.

[sandy666]

OT

What if there will be two authors for the same paper? (collective work)

[franck.so]

Thanks Guys,
Always good to have smart guys working when you're sleeping! :-) I will try both formula in my original table and see which one fit the best my purpose.
However, Sandy came up with a good question and she completely understood the purpose of this table.
So if anyone can improve the formula with this new variable: several authors could be shown as "TRUE" and then the result should send back all the corresponding names.
Thanks a lot for this good support!

Franck

[franck.so]

After trying both option:
The first one gave me an error on my table....I've inserted it with the CTRL+SHIFT+ENTER but did not work
The second one works fine. I will try to understand them both anyway because I did not work before with the "INDEX" and it seems that it is more powerful than VLOOKUP.
So if anyone can improve it giving the possibility to have several result answers if several column are sending "TRUE" for authors, that would be just wonderful!

Thanks!

Franck

[FlameRetired]

...........So if anyone can improve it giving the possibility to have several result answers if several column are sending "TRUE" for authors, that would be just wonderful!

Thanks!

Franck

I array-entered this formula in B2 of Results sheet, filled down and across to column M or until the multiples were exhausted.

Please Login or Register  to view this content.

The file is attached.

[sandy666]

@FlameRetired

I'm delighted with your formula
It should be explained step by step everywhere as a good example

regards
sandy

[FlameRetired]

Thank you for the feedback and kind words

[franck.so]

Whaoooo!
That's a good one for sure! It's getting a bit complex for me to understand it and I now see that I still have a lot to learn.
Thanks a lot for the time you afford me, really appreciated!
I will close the post.
A BIG THANK YOU!
Franck

[FlameRetired]

You're welcome...glad to help and thanks for the feedback.