# Index Match two worksheets

1. ## 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

2. ## Re: Index Match two worksheets

Try this

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

3. ## Re: Index Match two worksheets

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

4. ## Re: Index Match two worksheets

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.

5. ## Re: Index Match two worksheets

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

6. ## Re: Index Match two worksheets

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

Thanks so much for your help!

7. ## Re: Index Match two worksheets

The formula I posted was:
=INDEX(Sheet2!\$C\$7:\$C\$12, MATCH(1,(Sheet1!A4=Sheet2!\$A\$5)*(Sheet1!F2=Sheet2!\$E\$7:\$E\$10),0))

=INDEX(Sheet2!C7:E12,MATCH(Sheet1!A4,Sheet2!A2:A12,0),MATCH(Sheet1!F2,Sheet2!C7:E11,0))

Copy and paste my formula into the cell, then follow the final step as @JohnTopley mentioned.

Thanks

8. ## Re: Index Match two worksheets

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,(...?

9. ## Re: Index Match two worksheets

Post file showing incorrect results.

Attached.

Thank you!

11. ## Re: Index Match two worksheets

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

12. ## Re: Index Match two worksheets

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

13. ## Re: Index Match two worksheets

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

14. ## Re: Index Match two worksheets

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.

Thank you so much!

Christina

15. ## Re: Index Match two worksheets

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

16. ## Re: Index Match two worksheets

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

Lost cause with the fuel, right?

17. ## Re: Index Match two worksheets

in E6

``Please Login or Register  to view this content.``
...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.

Copy across to I6

Select E6:I6 and copy/paste to other ranges.

There are currently 1 users browsing this thread. (0 members and 1 guests)