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
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
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
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.............????
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?
Re: Looking up and using formulas from another WS
Thank you for your help. I will keep checking the posts. Anyway, you turned me on to "choose" which I had not used before. I already have some instances where I can use it. Thank you very much.............Dennis
Re: Looking up and using formulas from another WS
Right now I have about 35 different formulas (for 35 different shapes). The list could possible grow to about 50. Once the formula is established it will not change. The formula (or procedure) or function (once it is established) will be used every time a new worksheet is created that needs to use this formula (if that is what you mean). I was hoping there would be some way to name the formula and then to reference it that way.
In effect, what I am really trying to do is create a formula that replaces itself with another formula (which maybe is impossible).
If using VBA code and case loops (or something like that) is the only solution, then I guess that is what I will have to do. I am not experienced with writing code, so I would appreciate your help. Thank you very much.
Re: Looking up and using formulas from another WS
Chris:
Can you come up with any other solutions in the event where there would be many cases. The IF function restricts me to only 7 cases.
How to use (i.e import) formulas and text data from a table to another worksheet per attached example (from Table 1 to WS1). Thank you in advance for any help.
Please see attached spreadsheet for example. Thank you very much!
D3=Phil E3=Red F3= what is the formular for F3
Blue Green Red Orange Yellow
Frank 15 1 21 17 6
Joe 8 23 7 14 16
Phil 18 9 2 5 19
Bill 3 20 24 10 22
Charlie 13 12 4 25 11
What is the formula in F3 that would return value from the above table that
represents the intersection of the D3 Name from the Name Column and the E3 color
from the Color Row. The numbers in the table cannot be sorted.
In this case the answer would be 2.
