Vlookup in multiple areas

**jayna.kozak** · 07-23-2013, 12:51 PM

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** · 07-23-2013, 01:49 PM

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

Formula:

D8: =IF(C8>0,

IFERROR(VLOOKUP(C8,Audit!$B$9:$T$31,19,0),

IFERROR((8-VLOOKUP(C8,Audit!$V$10:$AF$11,11,0))/694,

IFERROR((28-VLOOKUP(C8,Audit!$V$15:$AF$16,11,0))/694,

IFERROR((6-VLOOKUP(C8,Audit!$V$20:$AF$23,11,0))/694,"No Results")))),"Not > 0")

Copy that formula down as far as you need.

Is that something you can work with?

**jayna.kozak** · 07-23-2013, 03:35 PM

I was able to make that work for me.

Thanks so much Ron!

Vlookup in multiple areas

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Vlookup in multiple areas

Re: Vlookup in multiple areas

Re: Vlookup in multiple areas

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count rows between two areas in a collection/selection of multiple areas

freezing multiple areas within a worksheet?

Multiple print areas

Multiple plot areas

[SOLVED] Set Multiple Print Areas

Bookmarks

Bookmarks

Posting Permissions