Did you array enter the formula?

Are you sure you have a row that has a match in both fields?

SMOKN_ENG_MOM wrote:

This returns #N/A

"Dave Peterson" wrote:

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

SMOKN_ENG_MOM wrote:

I have tried all these index / v look up solutions posted.
First sheet contains data base.....

A B D
1 1 A Happys bar and grill
2 1 B SOnny's bar and grill
3 2 A Macks sweat shop

On the second sheet, i want to enter a formula where i can look up
(second sheet Q5 value in first sheet column A) then (Second sheet Q6 Value
in first sheet column B) and
if both criteria match
return value into second sheet from first sheet column D

--

Dave Peterson


--

Dave Peterson