Referencing Two Excel Documents - Determine Value with Multiple Column Inputs

Question

I am trying to simplify this. Let's say I have the below Excel Tables (both are separate files).

Table 1 (csv for reference of columns - file is NOT CSV format though)

X52519, description1, vendor, Blue, Hard, Square    
X73849, Description2, vendorTwo, Red, Soft, Round    
X63825, Description3, vendorThree, Red, Hard, Fruit
X49582, Description4, vendorFive, Blue, Hard, Fruit

Table 2 (csv style for reference of columns)

Blue, Hard, Square, Lego
Blue, Hard, Round, Hula-Hoop
Red, Soft, Round, ball
Red, Hard, Fruit, apple
Orange, Soft, Fruit, Orange
Green, soft, small, Pea

I want table 1 to have a formula column on the ends of it and based on the results, show Table 2 Value. In real life, Tables 1 and 2 have many different columns and are more complex, but should be referenced. Table 2 is a master and holds more info such as descriptions, numbers, and such that are irrelevant to this post. I just wanted to explain so it wasn't an obvious solution to just add it to the first Spreadsheet. These two Documents must stay separate.

I want the final results of Table 1 to look like this: Table 1 (6 Columns)

X52519, description1, vendor, Blue, Hard, Square, Lego    
X73849, Description2, vendorTwo, Red, Soft, Round, Ball    
X63825, Description3, vendorThree, Red, Hard, Fruit, Apple
X49582, Description4, vendorFive, Blue, Hard, Fruit, Does Not Exist 

If a solution is not very easy to describe here, maybe some guidance. Ideally, I would want the Fourth column of Table 1 to auto-populate without doing anything, but a VB Macro would definitely be doable. Should I Import into Microsoft Access and use that? Just looking for some guidance and solutions if anybody has any ideas. I was told VLOOKUP would be very complex.

**EDIT As I'm reading this I'm finding more misunderstandings (I'm sure all on my part as I am new to the community. The "(If ...)" sections of table 2 were meant for Reference and were not part of the data. I was trying to say if the combination is blah, blah, blah from table 1, column D of table 1 should show apple because the other spreadsheet file says blah blah blah means apple. Does that clarify? I apologize for any confusion. I modified the question to hopefully clarify this better.

Answer 1

Assuming that your table 1 was on a sheet called Sheet1 and your table 2 was on a sheet called Sheet2 and both tables started in cell A1 of their respective sheets, a formula in cell D1 of sheet Sheet1 that will generate the values you are looking for could be:

=TRIM(LEFT(INDEX(Sheet2!$A$1:$A$3,(MATCH($A1&", "&$B1&", "&$C1&")",MID(Sheet2!$A$1:$A$3,FIND("(If ",Sheet2!$A$1:$A$3)+4,999),0))),FIND(" ",INDEX(Sheet2!$A$1:$A$3,(MATCH($A1&", "&$B1&", "&$C1&")",MID(Sheet2!$A$1:$A$3,FIND("(If ",Sheet2!$A$1:$A$3)+4,999),0))))-1))

That formula would need to be entered as a CSE formula (i.e. press Ctrl-Shift-Enter to "enter" the formula), and then it can be copied to cells D2 and D3.

Obviously, the references to sheets could be changed to refer to sheets in whichever workbooks you are using, and the cell references could also be adjusted to suit the location of your data within the sheets.

---

I strongly recommend that you don't use the formula (I only created it to prove to myself that I could, because I don't often use INDEX, MATCH, and especially CSE formulae), but instead think about reformatting your data (if possible) so that it is in a format that is more conducive to easier calculations.

Answer 2

Okay, if your Table1 looks like this:

And then your Table2 looks like this:

In G2 in Table1, you can use this array formula (meaning, enter with CTRL+SHIFT+ENTER

=INDEX(Table2!$D$1:$D$6,MATCH(Table1!D1&Table1!E1&Table1!F1,Table2!$A$1:$A$6&Table2!$B$1:$B$6&Table2!$C$1:$C$6,0))

and drag down.

The #N/A error means that you don't have that combination. You could wrap IfError() around the Index/Match to return something...say =IfError([formula above],"No such combination").