• Status: Solved
• Priority: Medium
• Security: Public
• Views: 470

# Vlookup based on 2 parameters? or another way to do this (index?)

Not sure how to do this. I don't think I can use vlookup for 2 parameters.

I want to look up a value in my dates worksheet based off of I2 and I6 on my OFD12 worksheet.

Example: If my  pay start date in OFD12!I2 is 3-6-11 and I am assigned to the C shift in OFD12!I6, then I want to return the value of 103  (dates!G7) in the OFD12!K3).

Thanks for any help
Brent expert-question-woc-total-hours.xls
0
bvanscoy678
• 3
• 2
• 2
2 Solutions

Senior ManagerCommented:
you can use combination of Index and Match. Use below

=INDEX(dates!A:G,MATCH(I2,dates!A:A,0),MATCH(I6,dates!1:1,0))
0

Senior ManagerCommented:
To explain what it does
I'm looking up row number for maching I2 using MATCH(I2,dates!A:A,0) and column number for matching I6 using MATCH(I6,dates!1:1,0)).

Finally am reaching 103 using row and col number as looked up above.
0

Commented:
Hello Brent,

Try this formula in K3

=INDEX(dates!E2:G27,MATCH(I2,dates!A2:A27),MATCH(I6,dates!E1:G1,0))

format as general, see attached

regards, barry
26905844.xlsx
0

Author Commented:
I tried both ways, for some reason I get a n/A returned.

I am using 2003 if that makes a difference.

thanks
pivot-ofd-12-VERSION-3.xls
0

Commented:
In that version you have changed the headers in dates!E1:G1 to show "a shift" etc. so that doesn't match with just "a" in I6 - if you change I6 to "a shift" (without quotes) then that should work - format K3 as general (otherwise you'll see a date format)

regards, barry
0

Author Commented:
Yes, I see that now.

thanks for both of the formulas. I will make the header changes and place the formula back in.

Off to solve more issues, I don't know about! Thanks
0

Author Commented:
thanks for both of the formulas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.