Hello everybody. I have just signed up in order to solve a problem that i have with excels if formula. My question is :
I have one cell A1 with value 11 and one cell B1 with value Nikos and red background colour.
I try to use the if formula like this : IF(A1>10,B1) so the cell A1 returns Nikos.


My problem is that i want it also to return the red colour of the cell B1 not only the value.
Thanks in advance.

Hi and welcome

there is no formula to do that if the formatting is not a result of conditional formatting. VBA is needed.
But why do you need that color?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for answering. I have created a calendar for reservations (e.g. hotel calendar)
One sheet is the calendar and another sheet has the reservations (arrival and departure dates)
On the reservations sheet I manually fill the arrival and departure dates and with the IF formula autofill the calendar with the names of the guests.
The problem is that on the calendar i want between different reservations different colours. So i want to write the name of the guest with a colour background and it autofills with name and colour.
I do not know many about excel , nothing about VBA and programming but i have a lot of patience.
Explain here with the formula that i wrote :
=IF(AND(F6>=Sheet2!$B$9,F6<Sheet2!$B$10),Sheet2!$B $3,IF(AND(F6>=Sheet2!$C$9,F6<Sheet2!$C$10),Sheet2! $C$3,IF(AND(F6>=Sheet2!$D$9,F6<Sheet2!$D$10),Sheet 2!$D$3,IF(AND(F6>=Sheet2!$E$9,F6<Sheet2!$E$10),She et2!$E$3,IF(AND(F6>=Sheet2!$F$9,F6<Sheet2!$F$10),S heet2!$F$3,IF(AND(F6>=Sheet2!$G$9,F6<Sheet2!$G$10) ,Sheet2!$G$3,IF(AND(F6>=Sheet2!$H$9,F6<Sheet2!$H$1 0),Sheet2!$H$3,IF(AND(F6>=Sheet2!$I$9,F6<Sheet2!$I $10),Sheet2!$I$3,IF(AND(F6>=Sheet2!$J$9,F6<Sheet2! $J$10),Sheet2!$J$3,IF(AND(F6>=Sheet2!$K$9,F6<Sheet 2!$K$10),Sheet2!$K$3,IF(AND(F6>=Sheet2!$L$9,F6<She et2!$L$10),Sheet2!$L$3,IF(AND(F6>=Sheet2!$M$9,F6<S heet2!$M$10),Sheet2!$M$3,IF(AND(F6>=Sheet2!$N$9,F6 <Sheet2!$N$10),Sheet2!$N$3,IF(AND(F6>=Sheet2!$O$9, F6<Sheet2!$O$10),Sheet2!$O$3,IF(AND(F6>=Sheet2!$P$ 9,F6<Sheet2!$P$10),Sheet2!$P$3,IF(AND(F6>=Sheet2!$ Q$9,F6<Sheet2!$Q$10),Sheet2!$Q$3,IF(AND(F6>=Sheet2 !$R$9,F6<Sheet2!$R$10),Sheet2!$R$3,IF(AND(F6>=Shee t2!$S$9,F6<Sheet2!$S$10),Sheet2!$S$3,IF(AND(F6>=Sh eet2!$T$9,F6<Sheet2!$T$10),Sheet2!$T$3,IF(AND(F6>= Sheet2!$U$9,F6<Sheet2!$U$10),Sheet2!$U$3,IF(AND(F6 >=Sheet2!$V$9,F6<Sheet2!$V$10),Sheet2!$V$3,IF(AND( F6>=Sheet2!$W$9,F6<Sheet2!$W$10),Sheet2!$W$3,IF(AN D(F6>=Sheet2!$X$9,F6<Sheet2!$X$10),Sheet2!$X$3,IF( AND(F6>=Sheet2!$Y$9,F6<Sheet2!$Y$10),Sheet2!$Y$3,I F(AND(F6>=Sheet2!$Z$9,F6<Sheet2!$Z$10),Sheet2!$Z$3 ,IF(AND(F6>=Sheet2!$AA$9,F6<Sheet2!$AA$10),Sheet2! $AA$3,IF(AND(F6>=Sheet2!$AB$9,F6<Sheet2!$AB$10),Sh eet2!$AB$3,IF(AND(F6>=Sheet2!$AC$9,F6<Sheet2!$AC$1 0),Sheet2!$AC$3,IF(AND(F6>=Sheet2!$AD$9,F6<Sheet2! $AD$10),Sheet2!$AD$3,IF(AND(F6>=Sheet2!$AE$9,F6<Sh eet2!$AE$10),Sheet2!$AE$3,IF(AND(F6>=Sheet2!$AF$9, F6<Sheet2!$AF$10),Sheet2!$AF$3,IF(AND(F6>=Sheet2!$ AG$9,F6<Sheet2!$AG$10),Sheet2!$AG$3,IF(AND(F6>=She et2!$AH$9,F6<Sheet2!$AH$10),Sheet2!$AH$3,IF(AND(F6 >=Sheet2!$AI$9,F6<Sheet2!$AI$10),Sheet2!$AI$3,IF(A ND(F6>=Sheet2!$AJ$9,F6<Sheet2!$AJ$10),Sheet2!$AJ$3 ,IF(AND(F6>=Sheet2!$AK$9,F6<Sheet2!$AK$10),Sheet2! $AK$3,IF(AND(F6>=Sheet2!$AL$9,F6<Sheet2!$AL$10),Sh eet2!$AL$3,IF(AND(F6>=Sheet2!$AM$9,F6<Sheet2!$AM$1 0),Sheet2!$AM$3,IF(AND(F6>=Sheet2!$AN$9,F6<Sheet2! $AN$10),Sheet2!$AN$3,IF(AND(F6>=Sheet2!$AO$9,F6<Sh eet2!$AO$10),Sheet2!$AO$3,IF(AND(F6>=Sheet2!$AP$9, F6<Sheet2!$AP$10),Sheet2!$AP$3,IF(AND(F6>=Sheet2!$ AQ$9,F6<Sheet2!$AQ$10),Sheet2!$AQ$3,IF(AND(F6>=She et2!$AR$9,F6<Sheet2!$AR$10),Sheet2!$AR$3,IF(AND(F6 >=Sheet2!$AS$9,F6<Sheet2!$AS$10),Sheet2!$AS$3,IF(A ND(F6>=Sheet2!$AT$9,F6<Sheet2!$AT$10),Sheet2!$AT$3 ,IF(AND(F6>=Sheet2!$AU$9,F6<Sheet2!$AU$10),Sheet2! $AU$3,IF(AND(F6>=Sheet2!$AV$9,F6<Sheet2!$AV$10),Sh eet2!$AV$3,IF(AND(F6>=Sheet2!$AW$9,F6<Sheet2!$AW$1 0),Sheet2!$AW$3,IF(AND(F6>=Sheet2!$AX$9,F6<Sheet2! $AX$10),Sheet2!$AX$3,IF(AND(F6>=Sheet2!$AY$9,F6<Sh eet2!$AY$10),Sheet2!$AY$3,IF(AND(F6>=Sheet2!$AZ$9, F6<Sheet2!$AZ$10),Sheet2!$AZ$3,"ΚΕΝΟ"))))))))))))) ))))))))))))))))))))))))))))))))))))))

I've seen very large formulas, but this one seems to hit the top ten

Perhaps post a small sample of your sheet with what you have and expected results.

We can start from there to simplify this monster formula and maybe find a solution to your color problem

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The ) at the end were found by pure luck.
Here is what i have created. (Imagine 1,101 and 2,102 Names and Surnames)

Attached Images

	Sheet1-Jpeg.jpg (44.9 KB, 14 views)
	Sheet2-Jpeg.jpg (27.9 KB, 13 views)

And here is what I want :

Attached Images

	Sheet1-Target-Jpeg.jpg (53.2 KB, 14 views)
	Sheet2-Target-Jpeg.jpg (28.1 KB, 13 views)

Please post a sheet. Pics are useless

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post