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

You can perhaps concatenate columns A and B on first sheet, and lookup in
that range of cells. Much easier.

Alternatively, if the combinations are unique, use a DGET function.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"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

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

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

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

Yes on the array and yes on the entry matches.
Yes it is pretty simple first column numbers second alphabet... thrid
column text title.

=INDEX('Utility base'!$D$3:$D$800,MATCH(1,'FORM 1001X RV 1'!Q5='Utility
base'!$A$3:$A$800)*('FORM 1001X RV 1'!Q6='Utility base'!$B$3:$B$800),0)

Question should i remove the title of the sheet it is actually on? (FORM
1001x RV1??

"Dave Peterson" wrote:

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

If the formula is in a cell on Form 1001x RV1, you don't want to include that in
your formula:

=INDEX('Utility base'!$D$3:$D$800,
MATCH(1,Q5='Utility base'!$A$3:$A$800)
*(Q6='Utility base'!$B$3:$B$800),0)

If you really did the array formula and you got #n/a, then what you think are
matches aren't really matches.

Maybe you have extra spaces in one of the cells (Q5 or A99 (say)). Or maybe
your numeric data isn't really a number in one of the cells (text vs number).

Try to eyeball the the matching row (say it's 99).

Then put
=q5='utility base'!A99
and
=q6='utility base'!b99
in a couple of empty cells.

You'll see at least one false. You'll have to fix those differences.

(Or modify the formula to take those differences into consideration.)


SMOKN_ENG_MOM wrote:

Yes on the array and yes on the entry matches.
Yes it is pretty simple first column numbers second alphabet... thrid
column text title.

=INDEX('Utility base'!$D$3:$D$800,MATCH(1,'FORM 1001X RV 1'!Q5='Utility
base'!$A$3:$A$800)*('FORM 1001X RV 1'!Q6='Utility base'!$B$3:$B$800),0)

Question should i remove the title of the sheet it is actually on? (FORM
1001x RV1??

"Dave Peterson" wrote:

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


--

Dave Peterson