Index/match across multiple sheets

I AM STUMPED ON THIS ONE! I have attached a "Mock"workbook to show what I am trying to accomplish. On sheet three column F i am trying to return a value from sheet 1 and 2. As of now I am only able to return the value from sheet 1. The attached Workbook explains it better on sheet 3. Any help on this would be greatly appreciated as I know your time is important to you.

Wow that was fast and Thank you very much. I wasnt aware of the &" "&. I was just using &. What exactly does the Trim do in the formula. Thanks again and I'll mark it solved

If value from the first INDEX doesn't exist you will get: " "&2nd INDEX value.... so TRIM remove extra space from the begining and from the end
Try to remove TRIM and check it by yourself

thanks for the feedback and mark thread solved

F2--3
f3--7
f4--2
f5--9
f6--43
f7--2

i'm trying to have sheet 3 return an exact match on either sheet 1...or sheet 2. sheet 3 needs to look at both sheets and only return an exact match if both columns on sheet 1 match both columns on sheet 3. or both columns on sheet 2 match both columns on sheet 3.
F2=3 because sheet 1 A2=aaa And sheet1 B2=sss...just like on sheet 3

F2=3 because sheet 1 A2=aaa And sheet1 B2=sss...just like on sheet 3

and sheet2!A2=aaa, sheet2!B2=sss ... like on sheet3
so which value is correct: 3 or 6 and why?
what kind of condition should be to select just 3 ?

i've uploaded a different sheet

ok, try this one with the first example:

Formula:

Please Login or Register  to view this content.

not sure if it worked for you but i just got #NAME

oh ok...right now i'm on a mac. i will try it on excel 2016 on monday. thanks alot and i'ts hard for me to believe how quickly you come up with formulas. Takes me awhile. Love learning it though. Thanks again

Glad to help

change in your profile Office version to which you most often use, less troubles

tested it on my MAC. works perfectly. Thanks again. You were a big help, headache is going away already. I'll look at my profile and edit which version i use the most

Or remember always give the version of excel for which you are looking for a solution in the first post

or you may try this.

i saw your are stated there have more than 10 sheets.
this may more simple to add sheets.

by replace the "" with
IFERROR(INDEX(Sheet4!$F$2:$F$7,MATCH(A2&B2,Sheet4!$A$2:$A$7&Sheet4!$B$2:$B$7,0)),"")
and edit with the sheet name (which bold)

=IFERROR(INDEX(Sheet1!$F$2:$F$7,MATCH(A2&B2,Sheet1!$A$2:$A$7&Sheet1!$B$2:$B$7,0)),IFERROR(INDEX(Sheet2!$F$2:$F$7,MATCH(A2&B2,Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7,0)),IFERROR(INDEX(Sheet4!$F$2:$F$7,MATCH(A2&B2,Sheet4!$A$2:$A$7&Sheet4!$B$2:$B$7,0)),"")))

This is an array formula. active by click ctrl + shift + Enter instead of Enter

you can see "{" in front of the formula. (eg: {=iferror....))))} )

thank

All of you people are awesome everything works great now Thanks alot

So I tried this and it stops at the first instance of an item... I need to find all instances where it matches Z5. Can anyone help?

Please Login or Register  to view this content.