Which Formula/Add-on should I use for this?

[larotth]

Oops, didn't realize that when I posted this it wouldn't keep the table formatting. Is there any way I can post an actual part that relates to my post?


Would really appreciate anyone's feedback on a workable formula for cell D5 below. I've tried lots of combos of the add-ons but I'm combining text with numbers, so not sure if Excel can handle that? Or maybe I'm just making it more difficult than it really is? : )


So, in plain english, what I want D5 to do (see table below) is:

- IF A4 OR B4 OR C4 = YES

- Then D5 = the amt on row E5 = to the column that has YES

- In the table below, B4 = YES, so I want D5 to return the value of 200


A B C D

1 Non- Max LIF Pymt Pymt

2 Qualified) based on

3 Min BC Federal <-----

4 0 YES 0 $ / MO

5 100.00 200.00 300.00 200.00


- In this table, A4 = YES, so I want D5 to return the value of 100


A B C D

1 Non- Max LIF Pymt Pymt

2 Qualified) based on

3 Min BC Federal <-----

4 YES 0 0 $ / MO

5 100.00 200.00 300.00 100.00

 

[SirJB7]

Hi, larotth!

Maybe the table (just first sample) is (@ separated), per row, beginning in column 1:


Non- @ Max @ LIF @ Pymt @ Pymt

Qualified) @ base @ on

Min @ BC @ Federal @ <-----

0 @ YES @ 0 @ $/MO

100.00 @ 200.00 @ 300.00 @ 200.00


If so:

a) the contents of rows 1 thru 3 are irrelevant

b) isn't D5 already filled with a value? (200.00)

c) the formula you asks for D5, will overwrite this value?

d) this formula should display 200.00 for the 1st. and 100.00 for the 2nd.?

e) where you say "Then D5..." you should say "Then E5..." (or other empty cell)?

f) where you say "the amount on row E5..." you should say "the amount on D1:D4?


If the answer is yes to all, try this, but in cell E5, for example:

=BUSCARH("YES";A4:D5;2;VERDADERO) -----> in english: =HLOOKUP("YES",A4:D5,2,TRUE)


But you should have always a "YES", otherwise N/A will appears. Avoid so, typing =ISERROR(HLOOKUP("YES",A4:D5,2,TRUE),0,HLOOKUP("YES",A4:D5,2,TRUE))

for displaying 0 zero instead.


Regards!

 

[NARAYANK991]

Hi ,


Try this formula in cell D5 :


=OFFSET($A$5,0,(MATCH("YES",$A$4:$C$4,0)-1))


Narayan


P.S. I have not included any IFERROR ! You have to decide what you wish to do in case YES does not appear in A4:C4.