vlookup - Matching two cells?

[jumuraa]

Is it possible to do the following?

Worksheet1!A2 holds a text value
Worksheet1!B2 holds a text value

Worksheet2!D2 holds a text value
Worksheet2!C2 holds a text value

Is it possible to use vlookup to find a match for Worksheet1!B2 in Worksheet2!C2 where the combination of Worksheet1!A2, Worksheet1!B2 is the same as the combination of Worksheet2!D2, Worksheet2!C2?

 

[buxtongt]

I would suggest making a third (hidden if you want) column in each sheet that is =value1&value2 and then using this to match with the vlookup. Hope this helps.

 

[jumuraa]

That would work, but then users would have to know the ID key. That's why I tried to do this with a database, but my office revolted.

A well, I guess the poor shlep after me will just have to keep copying info from one sheet to another.

Thanks for the help though.

 

[buxtongt]

What do you mean the users would need to know the ID key?

 

[jumuraa]

This is for a dynamic inventory tracking system. RecipientTracking! uses a vlookup for cascading drop down lists to control user choices. It is an ongoing list which gets rest each quarter.

I'm not sure how I would include hidden ID keys on both sheets and still do my list control, unless the user had to also enter it. If you can enlighten me I would be very greatful.

 

[buxtongt]

I'm not sure I understand exactly what you sheets are doing, but the column I'm talking about adding would be used only for the vlookups in question, so it shouldn't really impact anything else. You can also fill in the formula for the entire column and then hide it, so the user doesn't have to do anything with it, they just use the sheet as normal. Let me know if I'm missing something.

 

[jumuraa]

Its more likely that I'm not getting it...

This is a link to a tester spread sheet.

I have three sheets
MaterialsValidationTable! (Hidden)
RecipientTracking! For entering information for a specific order
InStock!For maintaining a single list of all items and their current amount in stock.

Currently I am using data validation to create linked dropdown lists on RecipientTracking! (Pulled from MaterialsValidationTable!). This includes a media type and the title of the item being sent. I am then using vlookup and a couple of IF statements to pull the Last known inventory stock taken from InStock! and display a value for Remaining Stock on RecipientTracking.

I've worked it out so that no matter how many entries there are for a single item, the value in Remaining Stock will be associated with the Media Type and Title from the drop down lists on RecipientTracking! & the corresponding inventory Item on InStock!.

I was trying to match those double values (MediaType+Title) in both worksheets in order to display the Remaining Stock value on the InStock! worksheet with its corresponding inventory item.

I have multiple Media Types for the same Titles, so your suggestion of an ID key makes sense. And it makes a lot of sense to have those IDkey's set and hidden.

The problem I have with your proposal, is I don't know how to keep my dropdown lists and associate them with an IDkey so that I can then compare the two IDkeys to pull the information I want from one sheet to the next.

Bear in mind that there are likely to be multiple entries for the same MediaType and Title, so this will likely require something that looks at the whole worksheet.

I really appriciate all the help I've gotten from this site so far on this. I wouldn't even be as far as I am without it. So, honestly, if no one has the time/inclination/knowledge to make this work... I will not be put out. My boss is already thrilled, I just wish I could have made it do what I envisioned when I started.

 

[buxtongt]

I'll poke around with this some this evening and get back to you.

 

[jumuraa]

I appriciate it. But please only do so if you have some free time and you really want to solve a puzzle.

Thank you.

 

[buxtongt]

Could you just set a cell equal to the two drop down values combined with the usual & and pull the vlookup from that instead of directly off of the drop downs? I haven't looked at your files in depth yet, but that's the first thing that comes to mind when reading your post again. Hope this helps.