3 column lookup for 4th column value

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

Guest

I have four columns of data. The first three are the lookup indicies for the
4th column I want to use as my result. Can Lookup, Index or Match do this?
Ken
 
Hi Ken,
One "back door" approach....
Insert a blank column D. In D1 enter the formula =A1 & B1 & C1 and copy down
all rows. Use this column as your looked up value (index).
Do the same thing for the values you want to use for the look up and use
VLOOKUP.
 
Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
 
If I understand you, say your data is in A1 to D100, and you want to return
the value in Column D, that is on the same row where your criteria is met in
the first 3 columns.

With the criteria for Column A entered in E1,
Column B in E2,
And Column C in E3,
Try this *array* formula:

=INDEX(D1:D100,MATCH(1,(A1:A100=E1)*(B1:B100=E2)*(C1:C100=E3),0))
 
Ken,

I like this since it is simple, but it didn't work initially. Until I found
that the lookup vector in the array had to be in Acending order.
 
Back
Top