combining multiple cells with IF statements

[googley]

I have 4 cells currently with the following formulas, I would like to combine this into one cell, if its possible?

=IF(D50="YES",0,IF($D$49<9,HLOOKUP($F$49,$Y$48:$AS$50,2,0)&" PSF",0))
=IF(D50="NO",0,IF($D$49<9,HLOOKUP($F$49,$Y$48:$AS$50,3,0)&" PSF",0))
=IF(D50="YES",0,IF(D49=9,HLOOKUP(F49,AV48:BP50,2,0)&" PSF",0))
=IF(D50="NO",0,IF($D$49=9,HLOOKUP($F$49,AV48:BP50,3,0)&" PSF",0))

 

[googley]

Hi,



Am Mon, 4 Nov 2013 08:40:17 -0800 (PST) schrieb googley:









try:

=IF(D49<9,HLOOKUP(F49,Y48:AS50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",IF(D49=9,HLOOKUP(F49,AV48:BP50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",0))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus,

Thank you that works fine! What if I add one other Statement?

IF(D49=5.5,HLOOKUP(F49,BS58:CM59,(D60="YES")*3+(D60="NO")*2,0)&" PSF"

since I have the <9 it results in an error, is there anyway to say <9 but> 5.5?

 

[Claus Busch]

Hi,

Am Mon, 4 Nov 2013 10:47:04 -0800 (PST) schrieb googley:

IF(D49=5.5,HLOOKUP(F49,BS58:CM59,(D60="YES")*3+(D60="NO")*2,0)&" PSF"

since I have the <9 it results in an error, is there anyway to say <9 but> 5.5?

the conditions will be calculated in there order of occurrence. So you
could solve it, if you put 5.5 at the beginning of the formula:
=IF(D49=5.5,HLOOKUP(F49,BS58:CM60,(D60="YES")*3+(D60="NO")*2,0)&" PSF",IF(D49<9,HLOOKUP(F49,Y48:AS50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",IF(D49=9,HLOOKUP(F49,AV48:BP50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",0)))


Regards
Claus B.

 

[googley]

Hi,



Am Mon, 4 Nov 2013 10:47:04 -0800 (PST) schrieb googley:






the conditions will be calculated in there order of occurrence. So you

could solve it, if you put 5.5 at the beginning of the formula:

=IF(D49=5.5,HLOOKUP(F49,BS58:CM60,(D60="YES")*3+(D60="NO")*2,0)&" PSF",IF(D49<9,HLOOKUP(F49,Y48:AS50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",IF(D49=9,HLOOKUP(F49,AV48:BP50,(D50="YES")*3+(D50="NO")*2,0)&" PSF",0)))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus,

Interesting it works Thanks. I did not understand the *3 and *2 this provides the row number in the Hlookup ??