Excel VLOOKUP/ IF/MATCH help needed

0
votes

Hoping someone can help me with an excel formula. I have two tabs (first tab would pull from second, second tab would be a report that gets copy pasted that sometimes has certain categories and sometimes it doesn't). What formula can i use to match a cell (line/column) if the column sometimes exists and sometimes it doesn't?). Would this be a match/if? I need the exact formula as I have tried many combos and it's just not working.

More concrete, daily sales on the first tab. The report that gets copy pasted in the second tab would have has sales categories (sometimes one of the categories gets pulled does not exist and gets replaced by another category) depending on what sales happen that day. The categories in columns would be "main dining", "library", "bar" and the sales categories by lines for each of these would be "Food", "Liquor", "beer".

I can provide additional information if needed.

Thank you for your help.

1
excelif-statementmatchvlookup
You could use a index/match formula with and iferror statement around it look for the alternative category if the value is not there. – VBA Pete
What about IfError()? It works like =IfError(Vlookup(...),"Not Found/Error"). But, personally, I like Index/Match, so I'd follow what @VBAPete suggests. – BruceWayne
just use T function – Grigory Ilizirov
Most people add a screenshot of data their issue, so that it can also help other users with similar issues. – VBA Pete
files.acrobat.com/a/preview/… – Ralu Burbano

1 Answers

0
votes

I am not sure what your sheets are called, so I left as sheet1 and sheet2, but the formula below should do what you are after. Please note that the categories need to be exactly the same, so you can't have "MDR" in one sheet and "Main Dinning" in the other.

=IfError(SUMPRODUCT((Sheet1!$A$2:$A$8=Sheet2!$A2)*(Sheet1!$B1:$F1=Sheet2!$B1),Sheet1!$B$2:$F$8),"0")

You should be able to paste this formula into cell B2 and then pull it down and right to populate the entire data sheet. For your info, Sheet1!$A$2:$A$8captures the row criteria range and Sheet1!$B1:$F1 the column criteria, while Sheet2!$A2 and Sheet2!$B1 are picking the criteria to look for. If the value cannot be found, the formula returns a 0.