Two colums match return text from another

[SMOKN_ENG_MOM]

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

 

[Wigi]

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.

 

[Dave Peterson]

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]

This returns #N/A

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

 

[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]

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]

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

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

 

[SMOKN_ENG_MOM]

Both cells came up as true and still am not getting a value.
When i select on the "!" to show calculation steps it shows the first array
results for each row.. (false,false, true, false, false, etc.) but for the
second match requirement is only shows the array as typed *(Q6='Utility
base'!$B$3:$B$800),
Then when i select evaluate I get

=INDEX('Utility base'!$D$3:$D$800,#N/A*(Q6='Utility base'!$B$3:$B$800),0)

I have formatted all cells to the same format (Text) since the numbers are
not going to be used in calculations.

 

[Dave Peterson]

Please post your formula.

It kind of looks like you have your ()'s messed up.

Both cells came up as true and still am not getting a value.
When i select on the "!" to show calculation steps it shows the first array
results for each row.. (false,false, true, false, false, etc.) but for the
second match requirement is only shows the array as typed *(Q6='Utility
base'!$B$3:$B$800),
Then when i select evaluate I get

=INDEX('Utility base'!$D$3:$D$800,#N/A*(Q6='Utility base'!$B$3:$B$800),0)

I have formatted all cells to the same format (Text) since the numbers are
not going to be used in calculations.

 

[SMOKN_ENG_MOM]

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

 

[Dave Peterson]

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

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

 

[SMOKN_ENG_MOM]

THat didn't fix it but i entered the criteria for Q5 and Q6 in blank cells
and changed the formula to reference those cells.... it worked.... so i drug
and dropped those cells into Q5 and Q6. It must have had something to do with
formatting but noting i could figure out so i just brought in clean cells. I
had re-formatted all cells to read the same...
Well it works now... thanks!

 

[Dave Peterson]

If you are matching on digits, then there is a difference between the number 123
and the text '123 (either 123 typed into a cell that was formatted as text or
typed with that leading apostrophe).

And changing the format won't change the value of the cell.

=vlookup() behaves the same.

Debra Dalgleish has lots of notes on troubleshooting this kind of error:
http://contextures.com/xlFunctions02.html#Trouble

THat didn't fix it but i entered the criteria for Q5 and Q6 in blank cells
and changed the formula to reference those cells.... it worked.... so i drug
and dropped those cells into Q5 and Q6. It must have had something to do with
formatting but noting i could figure out so i just brought in clean cells. I
had re-formatted all cells to read the same...
Well it works now... thanks!