Pick a value from a table ....

[David Evans]

OK Ladies and Gents - I need to pick a value from the UDF column that reflects the highest value in Rate having selected only those rows with an amount gretaer than zero ....

I've been trying to get sumproduct to produce an answer to no avail .....

The correct answer is UDF7, being the corresponding max rate for all amounts over 0

All help gratefully accepted as usual

D

 

[NARAYANK991]

Hi David ,

Try this :

=INDEX(D[UDF],MATCH(MAX(IF(D[Amount]>0,D[[ Rate]])),D[[ Rate]]*(D[Amount]>0),0))

or

=INDEX(D[UDF],MATCH(MAX(D[[ Rate]]*(D[Amount]>0)),D[[ Rate]]*(D[Amount]>0),0))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Khalid NGO]

Hi David,
using Narayan Sir formula without index:
="UDF"&MATCH(MAX(IF(D[Amount]>0,D[[ Rate]])),D[[ Rate]],0)

"UDF" can also be removed:
=MATCH(MAX(IF(D[Amount]>0,D[[ Rate]])),D[[ Rate]],0)

you just need to format your cell as:
"UDF"#

Enter with CSE

Regards,

 

[David Evans]

Gentlemen-
Many thanks for your help - I am getting ready to go to bed but will try out your ideas in the morning and I'm sure that they will be successful!

Thanks

D

 

[David Evans]

OK - I could not sleep on it - both ideas work perfectly -many thanks - the UDF column will hold the text for a User Defined Function that will calculate a value we require - this was I could not clear without your help!

Sincere thanks!
D