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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,136,289
Messages
5,674,882
Members
419,532
Latest member
longphamtel

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