CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
info_seekeR
info_seekeR

Reputation: 1326

How to use a cell content as a search criterion in an Excel table

I have an Excel table, named myTable, the contents of which are shown below:

Partners    2-Jan   3-Feb   25-Mar  27-Mar
O2          20       23       26      0
Vodafone    1        23       25      29
Vectone     0         0       50      60
Orange      25       12       34      15

I have two more cells that contain the search criteria for

  1. The Partners
  2. The Dates

For example, cell A1 contains '3-Feb', and cell A2 contains 'Vectone'.

I would like to use these 2 cells to make a selection from myTable. For example, using '3-Feb' and 'Vectone' would return 0 from myTable.

Thanks.

Upvotes: 0

Views: 97

Answers (2)

info_seekeR
info_seekeR

Reputation: 1326

Thanks to pnuts, whose answer I have adopted after slightly modifying it.

There were 2 problems faced in using cell contents as search criteria for myTable:

  1. myTable would surely expand in future, with more dates and partners. If we renamed the column and row labels for myTable, and then added another partner and some dates, we would have to assign the names for these new partners and dates manually.

  2. If I used the cell content of A1 directly, it resulted in an error. This is because the value in A1, 3-Feb, would be a date of a different format (2/3/2014) than that found in myTable (d-mmm). Hence, Match was not successful in this case. I have made use of Text function to convert the value in A1 to text in the required format, which resulted in a correct match.

The following is my answer adopted from @pnuts response.

=INDEX(myTable,MATCH(A2,myTable[Partners],0),MATCH(TEXT(A1,"d-mmm"),myTable[#Headers],0))

Here, myTable[Partners] refers to the column containing partner names, while myTable[#Headers] refers to the row containing dates. The Text function converts cell A1 to text, allowing Match to function.

The following was my initial attempt, but for fear of slowing down Excel in future, when there would be lots of more partners and dates, I do not prefer it.

=VLOOKUP(A2,myTable,MATCH(TEXT(A1,"d-mmm"),myTable[#Headers],0),FALSE)

Upvotes: 0

pnuts
pnuts

Reputation: 59495

=INDEX(MyTable,MATCH(A2,rLabels,0),MATCH(A1,cLabels,0))

INDEX
MATCH
named range

Upvotes: 1

Related Questions