I DON,T KNOW ABOUT "V-LOOKUP" & "H-LOOKUP"

LOOKUP_VALUE
I'll try to explain what i mean. My worksheet looks like this:

Column A contains values for "Type" (A1="Type", A2=1, A3=2)
Column B contains values for "Big" (B1="Big", B2=33, B3=22)
Column C contains values for "Medium" (C1="Medium", C2=22, C3=15)
Column D contains values for "Small" (D1="Small", D2=11, D3=9)

This means that Type 1 have the value 22 under "Medium" and Type 2 have the value 22 under "Big"

Lets say I input the value 1 in cell B7 and value 22 in cell B8.
Now I would like a formula in cell B9 that would return "Medium" because value 22 is found in column "Medium" for "type" 1.
And if I change the value in B7 to 2, then B9 should return "Big" because the value 22 is found in column "Big" for "Type" 2.
What would the formula be for cell B9? It should return either "Big, "Medium" or "Small" depending on the values in B7 and B8
2.TABLE_ARRAY
A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP.

The LOOKUP functions search the table array to find specific information.

For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data.
3.COL_INDEX_NUM
We found something interesting, but also annoying. It seems that Microsoft has changed the way the function VLOOKUP works in Excel 2007.

In my previous Excel version (2000) when I used VLOOKUP, I could later-on insert columns anywhere in the TABLE_ARRAY. Excel increased itself the COL_INDEX_NUM to find the original destination column.

Now in Excel 2007, if I insert a column in the TABLE_ARRAY, the COL_INDEX_NUM stays fixed. My vlookup brings up whatever the column left to the original destination column contains. I find this quite dangerous, it leads to unexpected results, if you don't have a close eye on it

4.RANGE_LOOKUP
Bottom of Form
Sub test()
Dim c As Range
Dim r As Range

Application.FindFormat.Clear
With Application.FindFormat.Borders(xlEdgeBottom)
.Weight = xlThick
.LineStyle = xlContinuous
End With

With Range("D10").Borders(xlEdgeBottom)
.Weight = xlThick
.LineStyle = xlContinuous
End With

Set r = Range("D1:D20")
Set c = r.Find(what:="", searchformat:=True)
If Not c Is Nothing Then
Debug.Print "Found"
End If
End Sub
Or
=INDEX(D24:D33,SUMPRODUCT((E24:E33=E7)*(F24:F33=F7)*(G24:G33=G7)*(H24:H33=H7)*(
I24:I33=I7)*ROW(A1:A1 0)))

The question looks to be abandoned by the user who asked it. If no action is taken within 2 days, a Quomon Moderator will consider closing the question and distributing the points.

The Quomon Team