2007 Table multi-column lookup formula?

hz79hc

New Member
Joined
Sep 16, 2009
Messages
27
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

 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
I think you will need the vlookup way, or MATCH perhaps
 

Forum statistics

Threads
1,141,044
Messages
5,703,919
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top