Disaggregated each works fine; MATCH works, inner VLOOKUP with MATCH works, but when I incorporated the outer VLOOKUP I get the error. Have stared at this for hours and don't know what is wrong. Please help!
=VLOOKUP(D9,VLOOKUP(A9,DataTable,MATCH("Health Plan",Data!$A$2:$BP$2,0),0),2,0)
What is this actually returning?
VLOOKUP(A9,DataTable,MATCH("Health Plan",Data!$A$2:$BP$2,0),0)
You have that placed in the first vlookup where the LookupRange is supposed to go.
So is the inside vlookup returning a range address string like "A1:B100" ?
Or perhaps a Text String that represents a named range?
You probably need Indirect
=VLOOKUP(D9,INDIRECT(VLOOKUP(A9,DataTable,MATCH("Health Plan",Data!$A$2:$BP$2,0),0)),2,0)
The out lookup needs to have a range of cells referenced and you have this instead but I could be wrong but those are my thoughts.
Please Login or Register to view this content.
Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.
Why not use sumproduct if you need to match and lookup stuff with certain criteria's?
=VLOOKUP(D9,VLOOKUP(A9,DataTable,MATCH("Health Plan",Data!$A$2:$BP$2,0),0),2,0)
That second VLOOKUP will return a single value, when the first VLOOKUP is expecting a range.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
The Match returns this:
53 =MATCH("Health Plan",Data!$A$2:$BP$2,0). Health Plan shows the employee's health plan choice (Individual or Family)
The inner VLOOKUP returns this:
Individual =VLOOKUP(A11,DataTable,MATCH("Health Plan",Data!$A$2:$BP$2,0),0). A11 has the employee name
The outer VLOOKUP returns this:
#VALUE! =VLOOKUP(D7,(VLOOKUP(A7,DataTable,MATCH(HealthPlan,Data!$A$2:$BP$2,0),0)),2). D7 is the employee's wage to be tested against either the Individual or Family table for the CoShare percentage the employee is required to pay.
If I place Individual or Family in the outer VLOOKUP it works.
The INDIRECT worked with a test formula. Now to apply the function to the actual workbook before checking this off as solved. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks