Finidng vlaues in a table.

[EFontana]

I'm familair with the VLOOKUP function for returning a value. I'm not sure how to return the same value when 2 criterias on the same row need to match.

During a VLOOKUP I need 2 values on the row from the first table to match the same 2 values on the second table and then return the value from the column I specify. The second table can have a range of several hundred rows.

How do I do this?

 

[Bernie Deitrick]

Say you have a table in A1:F18, and pairs of conditions in columns H:I, and
you want H to match A and I to match B, and return the value from column F.

Array enter (enter with Crtl-Shift-Enter)

=INDIRECT("F" &
MIN(IF(($A$1:$A$18=H1)*($B$1:$B$18=I1),ROW(($B$1:$B$18)),1000)))

This can be copied down to extract other values from column F to match the
conditions in other rows of H and I.

HTH,
Bernie
MS Excel MVP

I'm familair with the VLOOKUP function for returning a value. I'm not sure

how to return the same value when 2 criterias on the same row need to match.

During a VLOOKUP I need 2 values on the row from the first table to match

the same 2 values on the second table and then return the value from the
column I specify. The second table can have a range of several hundred
rows.

 

[EFontana]

Almost there. Let me clarify.

I have a table A1:F20, I have a second table L1:T20. I need A to match L and C to match M, and return the value from Column F where the match is true. The first table has 500 rows, the second tabel has 1200 rows.

Can you also clarify the Array Enter function.

 

[Alan Beban]

Almost there. Let me clarify.

I have a table A1:F20, I have a second table L1:T20. I need A to match L and C to match M, and return the value from Column F where the match is true. The first table has 500 rows, the second tabel has 1200 rows.

What do you want to return when the match is not true?

Alan Beban

 

[EFontana]

Nothing.

What do you want to return when the match is not true?

Alan Beban

 

[Bernie Deitrick]

E,

To get the value from column F, array enter (type in the following on one
line, then press Ctrl-Shift-Enter instead of just Enter):

=INDIRECT("F" &
MIN(IF((L1=$A$1:$A$500)*(M1=$C$1:$C$500),ROW(($A$1:$A$500)),1000)))

This will return the value from the first table where the value in column A
matches L1, and that in column C matches M1.

Copy the formula down for the 1200 rows to match your data in L1:M1200.

HTH,
Bernie
MS Excel MVP

Nothing.

match L and C to match M, and return the value from Column F where the match
is true. The first table has 500 rows, the second tabel has 1200 rows.