Trouble with using VLOOKUP + MATCH and MAX commands to return a string per a max

[wyczolko]

I am currently building a risk report model and have no problem utilizing VLOOKUP with MATCH and MAX to return the greater of two inputs in two categories. However when I apply the code to a logic prgoression so that the max can be returned as a string for another report tab, the code is simply returning the string corresponding to one column vectors input, rather than establishing the max of the two and returning the string for the max. The code is as follows ( I would greatly appreciate some help, Thanks) :

=IF(VLOOKUP(B4,database!$A$3:$AG$2000,MAX((MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=5,"VH",IF(VLOOKUP(B4,database!$A$3:$AG$2000,MAX((MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=4,"H",IF(VLOOKUP(B4,database!$A$3:$AG$2000,MAX((MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=3,"M",IF(VLOOKUP(B4,database!$A$3:$AG$2000,MAX((MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=2,"L","VL"))))

This is only reporting from the "Risk Impact, After Control, Non-Financial, 1 Yr" vector rather than establishing the max with the "Risk Impact, After Control, Financial, 1 Yr" vector

Daniel

[Richard Buttrey]

Hi,

Very difficult to assist without seeing your request in the context of its workbook. Please upload. In addition clearly show what you expect the result to be for all input permuataions - and importantly a description as to how you arrive at your answer.

Regards

[tigeravatar]

wyczolko,

I'm pretty sure this is what you want:
=INDEX({"VL","L","M","H","VH"},MAX(VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0),FALSE),VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0),FALSE)))

[Dionysos]

If I understand your formula correctly, your Max(Match()) is simply trying to establish which column header occurs last in the range. Since the columns are not dynamic, it will always return the same value. If you state that your results are only reporting from "Risk Impact, After Control, Non-Financial, 1 Yr", I can only assume that it occurs after "Risk Impact, After Control, Financial, 1 Yr" in your order of columns headers. I believe what you intend to do is the vlookup for each column prior to determining the max. To do this you will need to pull the max function outside the vlookup function, and vlookup both columns.

=IF(MAX(VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=5,"VH",IF(MAX(VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=4,"H",IF(MAX(VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=3,"M",IF(MAX(VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Financial, 1 Yr",database!$2:$2,0)),VLOOKUP(B4,database!$A$3:$AG$2000,MATCH("Risk Impact, After Control, Non-Financial, 1 Yr",database!$2:$2,0)))=2,"L","VL"))))