## Answers

Feb 17, 2012 - 03:17 AM

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

.Line

End With

With Range("D10").Borders(xlEdgeBottom)

.Weight = xlThick

.Line

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)))

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

.Line

End With

With Range("D10").Borders(xlEdgeBottom)

.Weight = xlThick

.Line

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)))

Add New Comment