Forum Discussion
Higher-End Excel Formula Help
Hello. Working on a massive spreadsheet. Background needed to understand question:
- You have (3) Columns, Column A, B and C
- These (3) columns are on Sheet "1"
- All three columns have the same 500+ entries
- Column A is based off a Data Validation list, and Column B is based off a different Data Validation list
- Column C is determined by the inputs of Column A and B based off an Index Match to a different table
HERE'S THE QUESTION:
I have another tab, say Sheet "2". I need a cell (Let's say D4) to fill with the value from Column C based off the inputs from Columns A and B. Here's the kicker....there may be multiple instances where Column A and B (say rows 40-80) have the same value, so rows 40-80 in Column C have the same output. For my case, that is OK & I need to return that value in Column C in rows 40-80.
I've tried a combination of things, including =IF(AND(VLOOKUP(....))) statements and can't seem to come to my answer. I can do a VLOOKUP by naming the whole 500+ entry table as an Array (say ARRAYINPUT) and have my column counter set to 3, but I can't seem to put it all together.
Any help would be appreciated. Thank you!
29 Replies
Andruw Peace be upon you, sir. I hope you are well. If you're interested in this formula, perhaps I can be of assistance? It is:
1. Using SUMPRODUCT and MATCH (Excel 2016 and later):
This approach utilizes the power of SUMPRODUCT and MATCH to find the first row meeting both criteria and return the corresponding value. Here's the formula:
Excel =SUMPRODUCT((A40:A80=A4)*(B40:B80=B4)*(ROW(C40:C80)-39),C40:C80)2. Using SUMIFS and INDEX (Works in all Excel versions):
This approach leverages SUMIFS to filter based on both criteria and then uses INDEX to pick the value from the filtered range. Here's the formula:
Excel =INDEX(C40:C80,SUMIFS(ROW(C40:C80)-39,A40:A80,A4,B40:B80,B4))
I would be grateful if you could remember me in your prayers- To achieve this in Excel, you can use an array formula with INDEX and MATCH. Since you have multiple instances where Columns A and B may have the same values, you need to use an array formula to handle this.
Assuming your data in Sheet "1" starts from row 2 (with headers in row 1), and your data in Sheet "2" starts from cell A2, you can use the following array formula in cell D4 of Sheet "2":
=IFERROR(INDEX('Sheet 1'!$C$2:$C$500, MATCH(1, ('Sheet 1'!$A$2:$A$500=A2)*('Sheet 1'!$B$2:$B$500=B2), 0)),"")
Here's how the formula works:
'Sheet 1'!$C$2:$C$500 is the range in Column C on Sheet "1" where the values to be returned are located.
MATCH(1, ('Sheet 1'!$A$2:$A$500=A2)*('Sheet 1'!$B$2:$B$500=B2), 0) searches for the first instance where both Column A and B match the criteria on Sheet "1". The multiplication (*) is used to create an array of 1s and 0s, where 1 indicates a match and 0 indicates no match.
INDEX is then used to return the corresponding value from Column C.
Remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. If done correctly, Excel will surround the formula with curly braces {}.
Now, you can drag this formula down in column D of Sheet "2" to apply it to other rows. Please see attached for file. I want to return the PANEL NAME based off the PANEL NUMBER and PHASE & POLE. It looks like I can't use and AND statements with multiple VLOOKUP functions in it, but I'm not sure how INDEX will work when the NUMBER and PHASE & POLE changes (need to have PANEL NAME show up on other tabs)
If it's what I think it is, performing a lookup where there's multiple returns for the same lookup value(s), it's very do-able with INDEX. A sample of the workbook would help.
Patrick2788 please see attached. I want to return the PANEL NAME based off the PANEL NUMBER and PHASE & POLE. It looks like I can't use and AND statements with multiple VLOOKUP functions in it, but I'm not sure how INDEX will work when the NUMBER and PHASE & POLE changes (need to have PANEL NAME show up on other tabs)
It's possible to avoid use of AND logic and modify the VLOOKUP to handle multiple criteria in lookup and table array if needed. I'm not clear on where (Sheet and cell) you need this formula.
- You will obtain help faster, if you attach your sample file.
