Index Match two worksheets

Hi! First time poster! Thank you so much for offering this forum.

Question: I would like to use the Index Match with multiple criteria across two worksheets. My other index match formulas work among other schedules however this time my second worksheet is full of other data which I wonder if is causing my formula to fail. Essentially, I need the fuel data from Sheet 2 to populate in Sheet 1 by unit. Sheet 2 worksheet is not setup like a standard schedule; I didn't create it- its received from a different department.

Attached is a sample.

Any insight is greatly appreciated!

Christina

Try this

=INDEX(Sheet2!$C$7:$C$12, MATCH(1,(Sheet1!A4=Sheet2!$A$5)*(Sheet1!F2=Sheet2!$E$7:$E$10),0))

Unless I am doing something wrong in the test sheet, it didn't work.

Formula needs to entered as below ....

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks @JohnTopley, I was pulled into a meeting needed to submit the formula

Attached the file with the {}, still not functioning.

Thanks so much for your help!

My apologies. The formula works for the sample sheet. However it doesn't work among the other cells after I adjust the formula ranges highlighted:
=INDEX(Sheet2!$C$7:$C$12, MATCH(1,(Sheet1!A4=Sheet2!$A$5)*(Sheet1!F2=Sheet2!$E$7:$E$10),0)) With my adjustments the formula continues to pull the first lookup. Could it have something to do with : ...,MATCH(1,(...?

Kindly advise.

Post file showing incorrect results.

Attached.

Thank you!

Your layout is a nightmare when it comes to creating formulae: I will look at shortly to try and find a solution.

OMG I know!!! But I cant recreate the wheel of the other department's long standing schedule. Thank you so much for your help!

in F6

=IFERROR(INDEX(Sheet1!$C$1:$C$1000,MATCH($A6,Sheet1!$A$1:$A$1000,0)+MATCH($F$4,OFFSET(Sheet1!$A$1,MATCH($A6,Sheet1!$A$1:$A$500,0),4,1000),0)),0)

Copy to F12 etc

I saved file as XLSX but you may want it as XLS (as per your original).

You are amazing for coming up with this however the sheet is so convoluted that the Quarles fuel is sometimes split between multiple states and also needs to be recorded as such - something I failed to mentioned and didn't realize ( again) until I updated it with your new formula.

This master sheet I have shared with you is used to report interstate fuel tax - mileage is also needed to complete this exercise. Perhaps you could assist with that? The mileage sheet is setup less whacky.

My index match formula "works" in that it returns a value but not the monthly total value needed. I haven't figured out how to fix that.
Please see attached.

Thank you so much!

Christina

For your mileage:

in U6 and copy across to AC

=IFERROR(INDEX(Sheet3!$H$1:$P$1000,MATCH($A6,Sheet3!$A$1:$A$1000,0)+MATCH("Total",OFFSET(Sheet3!$A$1,MATCH($A6,Sheet3!$A$1:$A$1000,0),0,1000),0),MATCH('1ST QTR'!U$4,Sheet3!$H$7:$P$7,0)),0)

Copy to other ranges as before

The mileage formula worked wonderfully!! Thank you!! You are an absolute genius!

Lost cause with the fuel, right?