formula to search text table

[hz79hc]

Hello,
I need to create a formula to search a Excel 2007 table of 200-300 rows. The table columns are almost all text values. I have tried to create a formula that is similar to selecting one value only in a filter on column A and selecting only one value in a filter for B. The formula needs to return the value from column C in the single table row that matches.

I am hoping to use structured references in the formula. Solution cannot use VBA or array formulas (sorry). I am hoping a solution exists that does not require an intermediate formula. Also like to avoid creating more columns that only exist to hold a concatenated text based on 2 existing columns. I am hoping the table rows will not have to be sorted by columns A and B. That is because I think I'll have more than 1 formula doing lookups, if someone out there can make a suggestion that works for my situation.

Data similar to this:
| A | B | C | D | E | F | G | H | I |
+---+---+---+---+---+---+---+---+---+
W X Y Z I J K L M
W 2X Y R I J V L M
W 2X Y Z I J V I M
W 3X Y Z I J K L M
B X Y Z I J K L M
M 2X Y L I J K L M
M 3X Y Z I J K L M
B X Y Z I J K L M

So I need to find the record having B='2X' and D='L' and return the value from column L (here it is 'L'). I wish there was more flexibility for VBA or use Access, etc.

This reminds me of SUMIFS because >1 criteria needs to be considered. But I need text search capability for texts (I hope that makes sense).

Hopefully I have missed a very easy way to do this.

Thanks for helpful ideas
Warren

 

[shg]

       A B- C D E F G H I --J--- K-
   1                      B Filt 2X
   2                      D Filt L 
   3   W X  Y Z I J K L M          
   4   W 2X Y R I J V L M          
   5   W 2X Y Z I J V I M          
   6   W 3X Y Z I J K L M          
   7   B X  Y Z I J K L M          
   8   M 2X Y L I J K L M        L 
   9   M 3X Y Z I J K L M          
  10   B X  Y Z I J K L M

The formula in K3 and down is =IF(AND(B3=K$1, D3=K$2), H3, "")

 

[hz79hc]

In other words, is there a way to do this without doing the concatenation in http://www.mrexcel.com/forum/showthread.php?t=497303
??

 

[alansidman]

Hello,

So I need to find the record having B='2X' and D='L' and return the value from column L (here it is 'L'). I wish there was more flexibility for VBA or use Access, etc.

Warren

Column L is not shown. Your data only goes out to column I. Did I mis-read it?

Alan