Return one value, Multiple criteria, Different tabs/sheets

[Hopethehip]

Hi everyone,

Sorry if this is a repeat thread.

Please see screenshots below.

I'm trying to move a value from Sheet 1 over to Sheet 2 based on multiple criteria. I got close with the formula below, but it looks like it is pulling the value from the FY17 column B (0) instead of FY19 column C (2,200). I cannot figure out how to add additional criteria in the formula to specify that I want the value from the FY19 column.

=IFERROR((VLOOKUP($B9,'Sheet 1'!$A:$C,MATCH(A$9,'Sheet 1'!$A$2:$C$2,0),FALSE)),0)

I also don't know if vLookup is the right choice or if I should be using Index/Match or another command. If anyone can help it would be greatly appreciated.

Thanks!

Capture.PNG

Capture1.PNG

[jeffreybrown]

Without a workbook example to test from, try this in C3 copied to D3 and down

=INDEX(Sheet1!B$3:B$12,MATCH($B3,Sheet1!$A$3:$A$12,0),MATCH($A3,Sheet1!$B$2:$C$2,0))

[Hopethehip]

Hi Jeff,

Thanks for the message.

As soon as I got your formula to work (It did. Thank you!) I realized that I didn't provide all the information necessary to describe the actual problem I'm facing in Excel.

Please see images below.

As you can see, I can't get the cells in Sheet 2 to populate based on both "Program" and "FY" information. Again, the formula I was using in Sheet 2 wasn't able to pull the values from the relevant cells in Sheet 1.

I hope this makes sense. Thanks again!

Capture3.PNG

Capture4.PNG

[jeffreybrown]

Please post a workbook, not an image.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Hopethehip]

Thanks Jeff.
Rookie mistake.

I think the attached workbook should suffice.

Tab 1 is the raw data, tab 2, is my 2nd attempt at getting the formula to work, and the last tab is the desired results I am hoping to automate through a formula.

Someone in my office mentioned that the formula might have a basic structure similar to =iferror(and(match...(match...))),"" though I don't know if that series is allowed.

If this doesn't make sense or if there is a problem with the file please let me know.
Thanks again!

[jeffreybrown]

Try this in C3 copied down and over...

=INDEX(Sheet1!$B$3:$G$12,MATCH($B3,Sheet1!$A$3:$A$12,0),MATCH($A3&"|"&C$1,Sheet1!$B$2:$G$2&"|"&Sheet1!$B$1:$G$1,0))

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

[Hopethehip]

By god, it's glorious!
Thank you, for both helping me solve this very useful and (now) straight forward Index and Match formula and for introducing me to arrays.
I will be sure to put all of this to good use moving forward.
Thank you again!!!

[jeffreybrown]

You are very welcome. Thanks for the feedback and the rep!