I am populating a 2007 Table and want a formula-driven method to look-up values in certain columns based on values in 5 other columns.
There can only be one record for each unique combination of values in these 5 columns.
Columns A to F are the category data (except column E is just an additional info field not essential to the lookup. G through at least V are numeric data and a few columns of text data follow..
The structured references used in formulas referencing Table data are new to me. Is there a way to explicitly set value of #ThisRow ? If there is, my problem may be solved. I doubt there is so let me continue.
I have only used structured references in the following so far (cells A1, A7, B8,etc holding values being "searched for"):
=SUMIFS(myTable[A],myTable,A1,myTable[C],A7, ..., myTable[G],B8)
Of course, since there is only one matching row, no summing actually occurs but the desired number is returned.
More importantly, this trickery doesn't work if I am trying to fetch a non-numeric value (returns zero).
Is there a good way to perform selection of a cell in the table based on values in specific columns?
If I were to invent the syntax maybe it would look like...
=myTable[V,#Row(AND(myTable[A."value"],myTable[A."value"], ... ,myTable[G."value"]))]
Simpler than this would be even better.
I will probably try using VLOOKUP of a new column that is the concatenation of the 5 columns but I am hoping to use the structrued reference no matter the method.
If I were loading this data into an array in VBA, A function to perform a search and return the value of interest could be built. I dont want to use any VBA because of my rust and especially because I am handing this off to another person (I hope).
Hopefully I have missed something simple and obvious.
Thanks for your suggestions (sorry for long message)
Warren
There can only be one record for each unique combination of values in these 5 columns.
Columns A to F are the category data (except column E is just an additional info field not essential to the lookup. G through at least V are numeric data and a few columns of text data follow..
The structured references used in formulas referencing Table data are new to me. Is there a way to explicitly set value of #ThisRow ? If there is, my problem may be solved. I doubt there is so let me continue.
I have only used structured references in the following so far (cells A1, A7, B8,etc holding values being "searched for"):
=SUMIFS(myTable[A],myTable,A1,myTable[C],A7, ..., myTable[G],B8)
Of course, since there is only one matching row, no summing actually occurs but the desired number is returned.
More importantly, this trickery doesn't work if I am trying to fetch a non-numeric value (returns zero).
Is there a good way to perform selection of a cell in the table based on values in specific columns?
If I were to invent the syntax maybe it would look like...
=myTable[V,#Row(AND(myTable[A."value"],myTable[A."value"], ... ,myTable[G."value"]))]
Simpler than this would be even better.
I will probably try using VLOOKUP of a new column that is the concatenation of the 5 columns but I am hoping to use the structrued reference no matter the method.
If I were loading this data into an array in VBA, A function to perform a search and return the value of interest could be built. I dont want to use any VBA because of my rust and especially because I am handing this off to another person (I hope).
Hopefully I have missed something simple and obvious.
Thanks for your suggestions (sorry for long message)
Warren
