VLook UP formula question

[Guest]

I need to write a vlookup on two variables.

I need it to match two variables (column A and column C) then go over five
columns for the answer.

Col. A Col. B Col. C Col. D Col. E Col. F
Month ID Name ..... Value

 

[JP]

Assuming your data starts in row 2 (with headers in row 1) and extends
down to row 100

=INDEX(F1:F100,MATCH(1,("Month"=A1:A100)*("ID"=B1:B100),0))

This is an array formula, use Ctrl-Shift-Enter to execute.

If you put the variables into separate cells (put month in H1 and Name
in I1) you can generalize the formula, for example:

=INDEX(F1:F100,MATCH(1,(H1=A1:A100)*(I1=B1:B100),0))


HTH,
JP

 

[Bernie Deitrick]

Scott,

If there is a unique combo of month and names, then use something like

=INDEX(F:F,SUMPRODUCT((A1:A1000 = "January")*(C1:C1000="Name")*ROW(A1:A1000)))

This assumes that the Month in column A are string, and not formatted dates.

HTH,
Bernie
MS Excel MVP