• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

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).

I have read about index, but still don't get that.

Thanks for any help
Brent expert-question-woc-total-hours.xls
0
bvanscoy678
Asked:
bvanscoy678
  • 3
  • 2
  • 2
2 Solutions
 
Jignesh TharSenior 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
 
Jignesh TharSenior 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
 
barry houdiniCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bvanscoy678Author 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
 
barry houdiniCommented:
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
 
bvanscoy678Author 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
 
bvanscoy678Author Commented:
thanks for both of the formulas.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now