Finidng vlaues in a table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

EFontana said:
I'm familair with the VLOOKUP function for returning a value. I'm not sure
Click to expand...
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
Click to expand...
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.
 
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.
 
EFontana said:
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.
Click to expand...
What do you want to return when the match is not true?

Alan Beban
 
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

EFontana said:
Nothing.
Click to expand...
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.
 
Back
Top