Solved: Help with lookup and reference

[Pizzafiend]

Greetings,
Any help with this problem would be greatly appreciated.

In Sheet!1 I have a persons name in A2 and a date in B1 I need to match the 2 criteria in sheet!2 and return a value that is 2 rows down.

Sheet1
A B C
1 Jul-05

2 John Doe


Sheet2
A B C D
1 Jul-04 John Doe

2 first Shift

3 200


Any suggestions?

[Ken Puls]

Hi there,

Can you just clarify?

Sheet 1
A2 = John Doe
B1 = Jul-05

Sheet 2
A1 = Jul-04
B1 = John Doe
A2 = First Shift
A3 = 200

Is that right? If not, maybe you could upload a small sample file with the info in it?

[Shazam]

Is this what you are looking for?

=HLOOKUP(A2&" "&B1,Sheet2!A3:A20,1,0)


Or


=INDEX(Sheet2!A2:A20,(MATCH(A2&" "&B1,Sheet2!A2:A20,0)+2))

[Pizzafiend]

Here is a sample.

[Shazam]

I see in your sample that you have 2 values. Do you want just to return 1 vaule or return 2 vaules or do you want to sum it?


Here what I got so far.

=INDEX(Sheet2!$B$2:$B$20,(MATCH(A2,Sheet2!$B$2:$B$11,0)*(MATCH(B1,Sheet2!$A $2:$A$11,0)+1)))

[austenr]

Maybe this:

=HLOOKUP(A2,Sheet2!A2:C11,4)

[Pizzafiend]

Thanks for all the help everyone. Got it to work!

[austenr]

Since you say your question has been answered I am going to go ahead and mark this solved. http://vbaexpress.com/forum/images/smilies/023.gif