Vlookup in multiple areas

[jayna.kozak]

Hello Excel community!

I've tried searching for an answer to my specific issue an have not had any luck yet.

What I have is an Audit template where there are 3 different areas that a result can be located in. They are:
B9:T31, V10:AF16 and V20:AF22.

What I have is a "Results" page where if the claim number is entered, it does a vlookup in the sheet and brings back the resulting deduction. This is what I have for the formula so far:

=IF(C8>0,IF(ISERROR(VLOOKUP(C8,Audit!$B$9:$T$31,19,)),(8-VLOOKUP(C8,Audit!$V$10:$AF$16,11,))/694,(28-VLOOKUP(C8,Audit!$B9:$T31,19,))/694)," ")

And this is working . . . kinda. It is only referencing the first two sections, and I don't know how to incorporate the other section.

So, I need to know how to incorporate the third section, which needs to be a 6-vlookup(C8,Audit!$V$20:$AF$22. Thoughts? Or is there a better way to do this?

Audit sample with percentage deductions.xlsm

[Ron Coderre]

Using your posted workbook...
This regular formula seems to return the results you're looking for

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

[jayna.kozak]

I was able to make that work for me.

Thanks so much Ron!