Excel to return a value in a column, if multiple criteria meets

[Ann Ooi]

I tried to return a value in sheet1, column A (vertical), to sheet 2 (Horizontal format), if the "Process_Step" and "WO" is matched. As the data is huge and there're many columns, I do not know how to specifically to use the index and match or vlookup, to return the column A value (Date) if the criteria are meet. The INDEX formula I written, is not working as I understand it's not making sense to Excel. [=IF(INDEX(WIP!$A$1:$W$8835,MATCH($B3,WIP!$U:$U,0),MATCH(C$2,WIP!$L:$L,0)),C3=WIP!$A:A,"")]

 

[Ann Ooi]

Hi Alan,
Noticed you were saying Power Query, so I checked online and found the software and downloaded. Sorry that I have never use this tool before. Can help to enlighten, how to apply the code on top? I am still exploring.

 

[Ann Ooi]

Hi Alan, I manage to explore till close and load to, but after that whenever I run the query, I cannot load the file to excel anymore? And for all the null setting, I have to select columns by column and having many replace value steps? If there's new data added, will the query still works and added the new data into the table? I'm not sure, am I do it right, hope able to set it to become a simple steps, so that other dummy like me, able to use it as well.

 

[alansidman]

If you add additional data to the original table that is brought into PQ, then click (possibly twice) on the Refresh All Icon, then PQ will update the output automatically. If you right click on the Connection only in the Query that you are showing above, it will give you the opportunity to load the results to a table in your spreadsheet.

For the nulls, you should be able to highlight all columns that you want to replace the nulls and do it at one time.

 

[Ann Ooi]

Hi Alan, So sorry, another question, what should I do with this error?

 

[alansidman]

In the Mcode, change the table name Table1 to whatever you have named your Excel table.

 

[Ann Ooi]

If you add additional data to the original table that is brought into PQ, then click (possibly twice) on the Refresh All Icon, then PQ will update the output automatically. If you right click on the Connection only in the Query that you are showing above, it will give you the opportunity to load the results to a table in your spreadsheet.

For the nulls, you should be able to highlight all columns that you want to replace the nulls and do it at one time.

HI Alan, Noted and thank you.

 

[Ann Ooi]

In the Mcode, change the table name Table1 to whatever you have named your Excel table.

Hi Alan, Just to confirm, if I share this file or send the file, then other users will be able to extract the query as well?

 

[alansidman]

So long as the original data is in the file and the output is in the file and not in a connection anyone receiving the file should be able to extract the data.

 

[Ann Ooi]

So long as the original data is in the file and the output is in the file and not in a connection anyone receiving the file should be able to extract the data.

if so, how to share the connection? so that they able to refresh and run the data?

 

[alansidman]

Cannot. You need to not have a connection as far as I know