Using a LOOKUP with duplicate values - return in an array in one cell

[mattdh12]

This is probably a very complicated formula. Basically, I'm trying to do a "trend if" based on a criteria. I have 2 tables. The first table is set up like this (actual table is close to 40000 rows):

names| known ys| known xs
bob 2 1
chris 4 2
sam 3 3
bob 5 4
chris 4 5
sam 6 6
bob 5 7
chris 7 8
sam 6 9
bob 9 10
chris 11 11
sam 10 12

The second table is set up like this:
name| new xs| y
bob 13
chris 14
sam 15

There is nothing in the y column in the second table - that is what I am trying to solve for. So for cell C2 in the 2nd table, I'd like to look at table 1 and only look at the "bob" rows and do an array trend formula based on that. I've seen similar complicated data extraction formulas but those require multiple cells.

[Vikas_Gautam]

Check if that's what you were trying to achieve..

=INDEX($F$3:$F$14,MATCH(K3&L3,INDEX($D$3:$D$14&$E$3:$E$14,0),0))

check the attached file...