Vlookup Q
 

Hello.

I have two worksheets.

Worksheet#1 has a unique ID field named ID which is populated, and some
other fields, including a field named Formtype that is populated, a field
named Hours that is blank.

Worksheet#2 has a matching ID field and Formtype field, both populated, in
addition to an hours field that is also populated with hours data.

What I am trying to do is use the VLOOKUP function to match the ID AND the
Formtype fields, and using these fields, find the corresponding hours value
on worksheet #2 and use this value to populate the Hours value on Worksheet#1.

Any ideas would be appreciated.

Thnak you.

Mark :)

Vlookup Q
 

You can do this fairly easily with index/match;

INDEX(Sheet1!A1:A10,MATCH(1,((Sheet1!B1:B10=idNum) *(Sheet1!C1:C10=formType)))

column A contains the value you want to return from the other sheet,
column B contains the ID numbers, and C contains the form type. of
course you'll change "idNum" and "formType" to whatever cell you have
to to identify that person and form.

For your specific problem, you would replace sheet1! in the formula
with worksheet2! and you would put the formula in worksheet1! with
idNum and formType pointing towards the cells on that row.

Vlookup Q
 

One way

=INDEX(Sheet2!$C$2:$C$10,MATCH(A2&B2,Sheet2!$A$2:$ A$10&Sheet2!$B$2:$B$10,0))

entered with ctrl + shift & enter





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"NWO" wrote in message
...
Hello.

I have two worksheets.

Worksheet#1 has a unique ID field named ID which is populated, and some
other fields, including a field named Formtype that is populated, a field
named Hours that is blank.

Worksheet#2 has a matching ID field and Formtype field, both populated, in
addition to an hours field that is also populated with hours data.

What I am trying to do is use the VLOOKUP function to match the ID AND the
Formtype fields, and using these fields, find the corresponding hours
value
on worksheet #2 and use this value to populate the Hours value on
Worksheet#1.

Any ideas would be appreciated.

Thnak you.

Mark :)

Vlookup Q
 

Thank you.

I tried the formula, but I receive a different number (the lowest number in
the range) than should be.

Here is my formula, based on your suggestion:

=INDEX(Hours_Data!$F$2:$F$6,MATCH(A5&E5,Hours_Data !$A$2:$A$6&Hours_Data!$E$2:$E$6,0))

(entered as an array formula)

where Hours_Data is worksheet2 from my post.

The columns on Hours_Data are as follows:

User_ID = Column A
Line_Number (same as Formtype) = Column E
Hours = Column F

I just choose rows 2 through 6 to start out as a test.

So, I guess my question is, where did I go wrong?

Please respond - thank you.

Mark :)
-----------------------

The corresponding columns on the Table_1 worksheet (worksheet1 on my post)
are as foolws:

User_ID = Column D
Line_Number (same as Formtype) = Column E
Hours = Column K

" wrote:

You can do this fairly easily with index/match;

INDEX(Sheet1!A1:A10,MATCH(1,((Sheet1!B1:B10=idNum) *(Sheet1!C1:C10=formType)))

column A contains the value you want to return from the other sheet,
column B contains the ID numbers, and C contains the form type. of
course you'll change "idNum" and "formType" to whatever cell you have
to to identify that person and form.

For your specific problem, you would replace sheet1! in the formula
with worksheet2! and you would put the formula in worksheet1! with
idNum and formType pointing towards the cells on that row.

Vlookup Q
 

Sorry, here si the formula that I meant to paste into my post:

=INDEX(Hours_Data!$F$2:$F$6,MATCH(0,((Hours_Data!$ A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2))))

Also, range Hours_Data!A2:A6 are all the same.

Thanks, and sorry for the inconvenience.

Still looking for a reply, though.

Mark :)
--------------
"NWO" wrote:

Thank you.

I tried the formula, but I receive a different number (the lowest number in
the range) than should be.

Here is my formula, based on your suggestion:

=INDEX(Hours_Data!$F$2:$F$6,MATCH(A5&E5,Hours_Data !$A$2:$A$6&Hours_Data!$E$2:$E$6,0))

(entered as an array formula)

where Hours_Data is worksheet2 from my post.

The columns on Hours_Data are as follows:

User_ID = Column A
Line_Number (same as Formtype) = Column E
Hours = Column F

I just choose rows 2 through 6 to start out as a test.

So, I guess my question is, where did I go wrong?

Please respond - thank you.

Mark :)
-----------------------

The corresponding columns on the Table_1 worksheet (worksheet1 on my post)
are as foolws:

User_ID = Column D
Line_Number (same as Formtype) = Column E
Hours = Column K

" wrote:

You can do this fairly easily with index/match;

INDEX(Sheet1!A1:A10,MATCH(1,((Sheet1!B1:B10=idNum) *(Sheet1!C1:C10=formType)))

column A contains the value you want to return from the other sheet,
column B contains the ID numbers, and C contains the form type. of
course you'll change "idNum" and "formType" to whatever cell you have
to to identify that person and form.

For your specific problem, you would replace sheet1! in the formula
with worksheet2! and you would put the formula in worksheet1! with
idNum and formType pointing towards the cells on that row.