I have a list of data for which I have to multiply the number of days a patient was in the hospital by the rate used for the type of stay it was. I have completed half of the formula for stays where the nurse has input comments so my formula right now looks like this in sheet 1, cell AP2 and then copied all the way down column AP (this formula pulls a portion of the text entered in the comment and matches it to the correct rate from a table at the bottom of the page)-this formula works for me as long as there is a comment in column AQ.

=IF(ISNUMBER(SEARCH("surg",AQ3)),PRODUCT(VLOOKUP(AC:AC,AN132:AT226,4,FALSE)*AJ3),IF(ISNUMBER(SEARCH("med",AQ3)),PRODUCT(VLOOKUP(AC:AC,AN132:AT226,3,FALSE)*AJ3),IF(ISNUMBER(SEARCH("icu",AQ3)),PRODUCT(VLOOKUP(AC:AC,AN132:AT226,5,FALSE)*AJ3),IF(ISNUMBER(SEARCH("maternity",AQ3)),PRODUCT(VLOOKUP(AC:AC,AN132:AT226,6,FALSE)*AJ3,IF(ISNUMBER(SEARCH("psych",AQ3)),PRODUCT(VLOOKUP(AC:AC,AN132:AT226,7,FALSE)*AJ3)))))))

I am trying to attach a screen shot of excel for both Sheet 1 and sheet 2 here: Sheet 1 1226.pngSheet 2 1226.png

There are a lot of stays where the nurses did not input comments in AQ on Sheet 1 so the cell has returned a #N/A. This is where I need formula help. For the cells with #N/A, I had to run a separate report which contains the data related to type of days and pull it into Sheet 2. I think I need to vlookup for data in cell A2 (unique ID) in sheet 1 and go to Sheet 2, column T-W and find the max value in those 4 columns. I then need to take the column that contains that max value and vlookup a portion of the text in the header for that column and multiply it by a dollar value in a table at the bottom of sheet 1 that corresponds to the column header in sheet 2.

I have tried VLOOKUPS and SUM(VLOOKUP) and even considered the INDEX but none of these fully gives me what I need. I know how to write the sum(VLOOKUP) portion, but cannot find information on how to vlookup text from the column that contains the max number.

Here are the details if I was doing this calculation manually:
Sheet 1 contains a list of unique ID's in cell A2. The first unique ID in cell A2 contains a #N/A in column AQ so I would go to sheet 2 and find the unique ID, then go to column T-W to find the column with the max value. If column T has the max value and the column is labeled "Medical Services", then I need to go back to Sheet 1 and find the total "days" spent in the hospital in column AJ, row 2 and multiply it by the rate in a table at the bottom of Sheet 1 that refers to the Medical Services rate for that particular provider/Hospital.

Can this be done? Should I try to use a Macro/VBA to do all of this instead?

Thank you!