MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP - calling all Excel Wizards! VLOOKUP and ...


Posted by Dustin on February 22, 2001 9:00 AM

Quick summary of my dilemma...
In my role of hr admin jockey, I have multiple worksheets to track:
1) Job orders that are open (Req Log) - (the Req # is the key field - no duplicates)
2) Candidates that have been submitted (Submittal Log) - (there may be multiple people attached to a single req, and/or a single person attached to multiple reqs - this is important for later on)
3) The details of any placements that have occured (Assignment Log)

In other places in the workbook, I have used to VLOOKUP to minimize duplicate entry of the same data on different worksheets, but this one has me stumped.

I want to use VLOOKUP to bring over the candidate's name and other details from the Submittal Log to the Assignment Log once a placement has been made, but due to the fact that there may be multiple submittals to the same req (as mentioned above), the VLOOKUP will not return the right value since there are multiple records that meet the lookup_value.

I need to find a way to cross-reference the VLOOKUP selection with another field (a field in which only the record associated with the placement has a "Yes" value in it) so that the function returns the appropriate data.

I know this is getting long-winded, but I figured you would need the details.

Any help would be greatly appreciated.

Thanks


Posted by Ian on February 22, 2001 9:57 AM

A lame suggestion

Have you considered a hidden column to concatenate your data and key your lookup off that. If in your submittal log, you have
A1- Name
B1- Yes
C1- =A1&B1

Would that help?

Posted by ian on February 22, 2001 10:02 AM

Re: A lame suggestion (2)

: I need to find a way to cross-reference the VLOOKUP selection with another field (a field in which only the record associated with the placement has a "Yes" value in it) so that the function returns the appropriate data.


Sorry, that was kind of burried above
Have you considered a hidden column to concatenate your data and key your lookup off that. If in your submittal log, you have
A1- Name
B1- Yes
C1- =A1&B1

Would that help?

Posted by cpod on February 22, 2001 10:05 AM


If the candidate names are in column A, the "Yes" designator is in column B and the name you are reseaching for is in D1 then:

MATCH(D1&"yes",A2:A8 & B2:B8)

Will give you the row within the array that you are seaching. Then you can use the index function to return data from any column in the array:

=INDEX(A2:C8,MATCH(D1&"yes",A2:A8 & B2:B8),3)

These are array functions and must be entered using Control+Shift+Enter

Posted by Dustin on February 22, 2001 11:01 AM

Thank you cpod and ian, I will try both options