formula require...

[VBABEGINER]

hi all,

I need formula, where lookup values are in two different columns, and with combination of these 2 values I want to find an answer....

[TABLE="width: 453"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]L1[/TD]
[TD]10[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L2[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L1[/TD]
[TD]30[/TD]
[TD]60[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value to find[/TD]
[TD][/TD]
[TD]Answer Require..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L1[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I use this formula, but these is something wrong..

=VLOOKUP(AND(A4,B4),A2:E4,3,FALSE)

 

[Scott T]

The part in red makes this an array formula. In order for excel to know that it is an array formula and calculated it correctly you need to use CSE to enter the formula.

=INDEX(E2:E4,MATCH(A7&B7,[COLOR=#ff0000]A2:A4&B2:B4[/COLOR],0))

https://support.office.com/en-us/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Some formulas do not need CSE because the argument can handle array operations
The Array_1, Array_2... argument in sumproduct

=SUMPRODUCT([COLOR=#ff0000]array1[/COLOR], [COLOR=#ff0000][array2][/COLOR], [COLOR=#ff0000][array3][/COLOR], ...)

The Lookup_vector argument in the LOOKUP and the result_Vector argument in the LOOKUP

=LOOKUP(lookup_value, [COLOR=#ff0000] lookup_vector[/COLOR],  [COLOR=#ff0000][result_vector][/COLOR])

The array argument in index

=INDEX([COLOR=#ff0000]array[/COLOR], row_num, [column_num])

The array argument in aggregate for function 14 to 19

=AGGREGATE(function_num, options, [COLOR=#ff0000]array[/COLOR], [k])

some arguments can never be arrays. This is also true of the __s versions like sumifs, countifs, averageifs

=VLOOKUP([COLOR=#ff0000]Lookup_Value,T[/COLOR]able_array, Col_index_num,[rage_lookup])
=HLOOKUP([COLOR=#ff0000]Lookup_value[/COLOR], Table_array,Row_index_number,[range_lookup])
=SUMIF([COLOR=#FF0000]range[/COLOR],criteria,[COLOR=#FF0000][sum_range][/COLOR])
 =COUNTIF([COLOR=#FF0000]range[/COLOR],criteria)
 =AVERAGEIF([COLOR=#FF0000]range[/COLOR],criteria,[COLOR=#FF0000][average_range][/COLOR])