Call in multiple entries from Master sheet

**bibleguy125** · 08-26-2011, 02:42 PM

Hey all,
I just recently figured out the VLOOKUP function and have been putting it to good use. My question is how do I return an entry when I don't have anything acting as a key. In most cases I can use a lot number for this, but we have a few lot numbers that are used by two or more of our clients at once. Is there a way to call in an entry based on two "lookup values' as the VLOOKUP formula calls it. I have a hunch I may be getting into array territory with this, but I'm not sure. Thanks!

**NBVC** · 08-26-2011, 03:24 PM

You can do it a few ways.

Easiest would be to Concatenate the 2 table columns to search in, and place that concatenated column to the left of the columns to return,

Then you can use Vlookup..

e.g.

=VLOOKUP(A1&"_"&B1,'Sheet2'!$C$1:$D$100,2,FALSE)

where A1 and B1 are the fields you want to combine for the search.

And Sheet2!C1:C100 contains the concatenated columns, of say columns A and B separated with an underscore to ensure robustness of the search.

e.g. =A1&"_"&B1 copied down

And then column D will be the column to return results from.

Another way is with no concatenation.

=INDEX(Sheet2!D1:D100,MATCH(1,INDEX((Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1),0),0))

this searches A1:A100 for a match to A1 on current sheet as well as searching B1:B100 on Sheet2 for B1 match, and returns what is in corresponding row of column D.

**bibleguy125** · 08-26-2011, 04:40 PM

Originally Posted by NBVC

And Sheet2!C1:C100 contains the concatenated columns, of say columns A and B separated with an underscore to ensure robustness of the search.

e.g. =A1&"_"&B1 copied down

That sounds like exactly what I need, I guess I'm not sure what you mean by this though. I won't have my two variables in the same columns as the master sheet if that matters. I have attached an example of what I want the finished product to look like. Do you mean that a column on the master sheet would need to be combined into one cell to be used as a key?

**NBVC** · 08-26-2011, 05:21 PM

Where is the database sheet with all the companies, lots and variables you want to pull over? And where in the master sheet is the information supposed to go?

**bibleguy125** · 08-29-2011, 09:13 AM

Here is just a basic example. The lot and company go where indicated and is what I want to use as the search parameters. I just need to figure out how to use the two search parameters with VLOOKUP. With what you were saying before, do I need to insert a column before A on the database sheet and combine the company column and the lot column to make a unique key? Thanks a lot!

**bibleguy125** · 08-29-2011, 09:23 AM

I tried what I was talking about and it seems to work. Is this what you were talking about, or is there a better way that I'm missing?

**NBVC** · 08-29-2011, 09:26 AM

See attached for the suggestion,

Inserted a new column before column A on the Master sheet, and inserted formula:

=B2&"_"&H2

copied down.

Then in the Output sheet, you enter the company and lot Id's, and formula, for example in B3 is:

=IF(G3="","",VLOOKUP($F$1&"_"&G3,'Master-Database'!$A:$T,3,FALSE))

copied down.

For the other columns, you need the same formula and just change the 3 to the respective corresponding column number in the Master table.