formula to find value from two separate tables and based on values in three tables-CodePudding

I'm looking for a formula for the Party column in Table 3 that will produce its values based on the data contained in Table 1 and Table 2.

NumSelect value in Table 3 determines Party value in Table 3.
Where NumSelect has "p", it refers to data in Table 1. If no "p" in NumSelect, then it refers to Table 2.
Number in NumSelect refers to row number.
If the corresponding ShortName has a value, that value should be returned.
If the corresponding ShortName is blank, then the corresponding Name should be returned.
Uppercase "P" and lowercase "p" in the NumSelect should both point to Table 1.
Each table is an Excel Table and its rows may expand or contract.
Certain rows in Table 1 and Table 2 may be empty.
Formula should not be volatile, not require control shift enter to enter the formula, and not require VBA.

Thanks!

Sorry for the bad formatting. I had this question formatted perfectly, but Stack Overflow kept preventing me from posting it because it claimed, "Your post appears to contain code that is not properly formatted as code. Please indent all code by 4 spaces using the code toolbar button or the CTRL K keyboard shortcut. For more editing help, click the [?] toolbar icon."

Table 1

Name	Gender	ShortName	Occupation
Grace Turner	F		Singer
Cadie Crawford	F	Tiger	Fine Artist
Paige Johnston	F		Archeologist
Dexter Payne	M	Klondike	Veterinarian
Valeria Barnes	F		Chef
Florrie Reed	F		Lawer
Emily Ferguson	F		Scientist
Sam Hawkins	M	Alpha	Biochemist
Savana Ellis	F		Cook

Table 2

Name	Gender	ShortName	Occupation
Vanessa Cooper	F		Producer
Jasmine Morris	F	Beta	Baker
Evelyn Taylor	F		Economist
Adelaide Roberts	F		Historian
Blake Cunningham	M	Lion	Chef
Adelaide Harrison	F		Chemist
Frederick Watson	M		Journalist

Table 3

NumSelect	Party
p2	Tiger
3	Evelyn Taylor
P8	Alpha
2	Beta
7	Frederick Watson
p7	Emily Ferguson

CodePudding user response：

Welp, I figured out the formula. But it's very inefficient. I'm sure someone here could make it a lot shorter and more efficient.

Here it is:

=IF( INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[[Name]:[ShortName]],Table2[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[Name],Table2[Name])<>""),SUBSTITUTE(LOWER([@NumSelect]),"p",""),3) =0,

INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[[Name]:[ShortName]],Table2[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[Name],Table2[Name])<>""),SUBSTITUTE(LOWER([@NumSelect]),"p",""),1),

INDEX(FILTER(CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[[Name]:[ShortName]],Table2[[Name]:[ShortName]]),CHOOSE(IF(LOWER(LEFT([@NumSelect],1))="p",1,2),Table1[Name],Table2[Name])<>""),SUBSTITUTE(LOWER([@NumSelect]),"p",""),3) )

CodePudding user response：

Long Formula

Your formula has 717 characters, this one has 347.

=IF(ISNUMBER(SEARCH("P",[@NumSelect])),
    IF(INDEX(Table1[ShortName],VALUE(RIGHT([@NumSelect],1)))="",
        INDEX(Table1[Name],VALUE(RIGHT([@NumSelect],1))),
        INDEX(Table1[ShortName],VALUE(RIGHT([@NumSelect],1)))),
    IF(INDEX(Table2[ShortName],[@NumSelect])="",
        INDEX(Table2[Name],[@NumSelect]),
        INDEX(Table2[ShortName],[@NumSelect])))

A pseudo-code could look like this:

=IF(ISNUMBER(A),IF(B="",C,B),IF(D="",E,D))

The issue is that B (lines 2 & 4) and D (lines 5 & 7) are repeated expressions.
Hopefully, this will help someone to make a major improvement.