# Lookup Formula - Intersection Value

• In the attached lookup table what would be the lookup formula in F22 that would return the appropriate Fabrication Table No. which would be at the intersection of the Item Key (range A3:A15) and the selected column heading (LP, MP, or HP in range I3:K15), which in this example is “MP”

In this example, what is the lookup formula that would return Table No “44”? I have no problem creating the formula if the Item Key Column (A) was instead in Column (H) and adjacent to the three columns in the Fabrication Table. Can you help me with this problem where you deal with two separate isolated ranges within a large table?

## Files

• Re: Lookup Formula - Intersection Value

Not too sure I grasp what you need but try the formula below in F22

=INDEX(\$A\$3:\$N\$15,\$D\$22,MATCH(\$E\$22,\$A\$3:\$N\$3,0))

• Re: Lookup Formula - Intersection Value

I had tried that, and it does not work. The problem is when you index the entire table, things get all out of wack. Sometimes you get the proper answer and sometimes you get a value from some other cell in the table. The value has to come from the range of I3:K15. With this formula and others that I tried, depending on the values that I entered in D22 & E22 I sometimes got values from the correct range (I3:K15) and sometimes the values came from other parts of the entire table (A3:N15). I think that the trick is to not refer to the entire table, but only to the ranges within the table that have the variables that you are dealing with, namely those in the yellow and blue pattern colors..........Any other suggestions.............????

• Re: Lookup Formula - Intersection Value

Can you post an example of where the formula fails?

• Re: Lookup Formula - Intersection Value

Sure Here are two examples

Enter Item Key No. "1" in cell D22 and "MP" in cell E22 should return "41" but you get "MP" instead
Enter Item Key No. "2" in cell D22 and "LP" should return "1" but you get "44" instead.

???????

• Re: Lookup Formula - Intersection Value

Try

=INDEX(\$A\$4:\$N\$15,\$D\$22,MATCH(\$E\$22,\$A\$3:\$N\$3,0))

• Re: Lookup Formula - Intersection Value

OK. Thank you very much. That works. However, I think judging by your solution that there is no way that I can use this same table (which is actually an excerpt from a much larger table) to find, for example, the "Field Install" table numbers in the next three columns (L, M, & N) that would also correspond to the same Item Key numbers (in Column A).

That because these next three columns have identical column headings (LP, MP, & HP) as those in coumns I, J, & K. The lookup formula would not know which LP or MP, or HP was being referred to. Right?

Maybe the only solution is to effectively have a separate table (sub table) for each three-column array of data (with each of these tables having an additional column for the "Item Key" values?

I just thought there might be an easier way??? Anyway, again, thank you for your help. But if you have a solution to this much more complex problem, I would appreciate it. I was thinking some more complex and involved usage of the index and match functions might work but at this time seem to be beyond my excel experience.

• Re: Lookup Formula - Intersection Value

Quote

That because these next three columns have identical column headings (LP, MP, & HP) as those in coumns I, J, & K. The lookup formula would not know which LP or MP, or HP was being referred to. Right?

It will stop at the first occurence. You could, if needed, name the second lot of identical headings something like LP2 etc. It's never a good idea to have identical column headings for the one table.

I don't see any occurence where the formula would fail though as you always want the first occurrence?

• Re: Lookup Formula - Intersection Value

No, thats the point. I would in fact not always want the first occurance. Sometimes it would be the second, sometimes the third, etc. etc. But I think that I will pursue your idea of creating unique column headings or using concatenation. Thank you again for all your help.............Dennis

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!