return a value based on condition in 2 columns

Originally Posted by mrice

You could add a new column to each sheet which concatenates the store and product.

e.g. = A2 & "/" & B2

You could then base your look-ups on this.

Thanks, but that would be unproductive in mty case as I have enough junk data on that spreadsheet.

Try this formula in B2:

Originally Posted by NBVC

Code:
=IF(ISNUMBER(MATCH(1,([raw_data.xls]Sheet1!$A$2:$A$11=$A2)*([raw_data.xls]Sheet1!$B$2:$B$11=B$1),0)),INDEX([raw_data.xls]Sheet1!$C$2:$C$11,MATCH(1,([raw_data.xls]Sheet1!$A$2:$A$11=$A2)*([raw_data.xls]Sheet1!$B$2:$B$11=B$1),0)),"")
adjust ranges to suit if you have more data.... then confirm the formula with CTRL+SHIFT+ENTER not just ENTER you will see { } brackets appear around the formula.

Then copy down and across your table.

I know you're a MOD and all, but that is quite ingenious solution. Thank you very much. It works well on the test files now I just have to implement it in my live ridiculously huge spreadsheet.

Really appreciate the help.

George

Originally Posted by NBVC

Try this formula in B2:

Please Login or Register  to view this content.

adjust ranges to suit if you have more data.... then confirm the formula with CTRL+SHIFT+ENTER not just ENTER you will see { } brackets appear around the formula.

Then copy down and across your table.

How to make the function replace all blanks I get from using the above function with "0" as I get #VALUE if any operation is performed with the blank cell?

Thank you,

George

Originally Posted by NBVC

I'm not sure what you mean....

if there is no matching combination in columns A and B, you will get a blank... but not #VALUE! error....

You will get that error if you didn't confirm the formula with the Ctrl+Shift+Enter keys....

If you want 0 instead of blank returned when no matches found, simply replace the last "" with a 0, in the formula.... but you must reconfirm with the CSE keys each time you make a change.

Thank you, it worked I did try to change that myself but haven’t done the CSE combination so it wasn’t working. I need to perform operations with those cells and excel was giving me the #VALUE! error when it encountered a blank cell in the formula.

Once again I'm grateful for your help.

George