Cell intersection lookup in excel

[Sayam]

Hi friends,

I need to have a two dimensional search here i can populate a cell which contains data and another one same contents at it's intersection of a row and column each of which will be having some values to it. In shot i need to have combination of HLOOKUP and MATCH functions should do it. I have asked my friends about the same they told me that it's possible but don't know the way can anyone help me out and i want this thing to be done in Excel 2007

[Sophia]

To have a look up with two dimensions you need to follow the below steps:-

1. In the sample worksheet, click on the Page Views tab, and then clear the VLOOKUP and HLOOKUP formulas and all text in Columns E1 and F1.In Cell F4, type:
   
   =VLOOKUP(
   
   
2. Click on lookup_value in the ToolTip box , and then click Cell F3. Excel writes the value F3 next to the opening parenthesis.
   
   
3. Insert comma after F3 in the formula, and then click on the Pages tab.
   
   
4. In the Pages worksheet, highlight the cell range A3 to B30. The string Pages!A3:B30 appears in the formula bar.
   
   
5. In the formula bar, type a comma after Pages!A3:B30. Entering a comma moves you to the next option in the ToolTip.
   
   
6. Enter the rest of the formula as shown: 2,false), and then press ENTER. Excel returns you to the Page Views worksheet, and #N/A appears in Cell F4, indicating that you need to enter a search value in Cell F3.
   
   
7. Now finally copy one of the page ID values from Column A to Cell F3, and then press ENTER. The corresponding page name appears in Cell F4.

[Harshini]

In this case i would recommend you to use the following formula for two matches to feed to the index function:

=INDEX(A2:G22,MATCH(J3,A2:A22),MATCH(I3,A2:G1,0))

where I3 is the size and J3 is the rating

[Sam.D]

If you are not able to understand anything then i would suggest you to have a look at match function in help section which will give you an idea of how to use it in that you will require a third parameter , Match_type, to the MATCH function and set this parameter to 0 (zero) as your data in the $A$2:$A$13 range is not sorted.