User Posts: Masum Mahdy
0
Area Under Curve Using Integration in Excel (with Detailed Steps)
0

This article will illustrate how to calculate area under a curve using integration in Excel with instructional images and detailed discussion. ...

0
Macro to Create New Sheet and Copy Data in Excel (4 Examples)
0

If you want to easily open new worksheets and copy data, using Excel Macro is a perfect choice. In this article, I will show you how to use Excel macro to ...

0
How to Create Investment Property Cash Flow Calculator in Excel
0

This article will discuss Investment Property Cash Flow, and components of cash flow. And it will also give you simple and easy guidelines on how to make ...

0
How to Plot Poisson Distribution in Excel (with Easy Steps)
0

This article will describe how to plot Poisson distribution in Excel based on sample data. First, we will discuss what Poisson distribution is and what it does ...

0
Excel Chart Not Updating with New Data (2 Suitable Solutions)
0

If you are in a situation where your Excel chart is not updating with new data, and searching for solutions, you are in the right place. In this article, I ...

0
How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)
0

In this article, I will show you how to make a scatter plot in Excel with two sets of data. A scatter plot or an XY graph (also known as scatter diagrams) is a ...

0
How to Forecast in Excel Based on Historical Data (4 Suitable Methods)
0

Excel has awesome tools and functions to predict future values. It has a Forecast button, introduced in Excel 2016 version, FORECAST, and other functions both ...

0
Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
0

This article will show how to transpose multiple columns into one column in Excel using Excel formulas, VBA codes, and the Power Query tool. The following ...

0
Convert Excel to Text File with Delimiter (2 Easy Approaches)
0

Text files consume less memory and are faster than Excel spreadsheets. Delimiters make the text files more readable, especially those which have numeric data ...

0
How to Separate First Middle and Last Name in Excel Using Formula
0

This article will show how to separate the first name, middle name, and last name with the help of an Excel formula. To get a quick view on this, have a look ...

0
How to Use Excel ADDRESS Function (7 Examples)
1

The ADDRESS function in MS Excel is under the Lookup and Reference functions category. We use the ADDRESS function to get the address of a cell (e.g. A45) in ...

0
How to Combine Cells in Excel (4 Methods + Shortcut)
1

While dealing with Microsoft Excel, we often have to combine values in cells in different situations. MS Excel has numerous ways to join cells, and we will see ...

0
Delete Blank Rows in Excel (8 Suitable Methods)
1

Imagine a case when you have a set of data in an Excel worksheet, and you see some unnecessary blank rows. No doubt, such unexpected empty rows annoy everyone, ...

0
How to Copy Formula in Excel (6 Quick Methods)
1

While dealing with MS Excel, we often have to deal with an Excel formula, copy it to different cells, columns or rows. There are several methods to copy a ...

0
Percentage Formula in Excel (6 Examples)
1

In Microsoft Excel, we can use different methods to calculate the percentage of a numeric value with a formula. We can apply algebraic calculations or insert a ...

Browsing All Comments By: Masum Mahdy
  1. Thank you for your compliment, David!
    Please click on the following link for more examples of the INDEX-MATCH combo:
    https://www.exceldemy.com/tag/index-match-excel/

  2. Hi AKSHAY THAKKER! We hope you are well. It’s been 6 years since you posted this query here. We are extremely sorry for being so late in responding. Hope you would have had a solution to your problem somewhere by now. However, we are providing a solution to your question hoping that other readers might find it useful.
    For example, we have created a 10×10 array using the following formula.

    =IF((ROW(A1:A10)-(COUNT(A1:J1)-COLUMN($A$1:$J$1)))>1,0,($A$1:$A$10)*(IF(A1:J1=A1:J1,1)))


    Look at the following image.

    You must do two things before applying the formula.
    First, place the numbers in the first row (row 1, i.e. row of A1) serially, and second, place them serially down the first column (column A). You can place them elsewhere, but in that case, you have to change the cell references in the array formula accordingly.
    You can create any square array with your desired sequence (1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0) having any square dimension. However, if you want to change the sequence, you have to change the formula a bit.
    Tip: Look into the greater than logic in the formula. You have to make the change here to create other sequences.

    If there is any query, please let us know. You can also send us your problem at this address: [email protected]

  3. You are welcome, Surya 🙂

  4. You are welcome, Ahmad 🙂

  5. Hello Jay, thanks for your nice words. Please visit our blog for more array posts! We have written more posts like this time by time.

  6. You are welcome, Surya! 🙂

  7. Thank you so much, Ferreira. Visit our blog and explore more!

  8. Hi Bill Coriell! Sorry for being late in reply. Maybe you were asking for the following article.
    https://www.exceldemy.com/what-is-an-array-in-excel/
    With regards
    -ExelDemy team

  9. Dear pat, sorry for replying late. Please send your file to [email protected] so that we can help. Thanks for commenting.

  10. We are extremely sorry Hannah W. to know that you have faced this difficulty in applying our provided code! We will add the explanation here as soon as possible. Stay with us.
    With regards
    -ExcelDemy team

  11. The code is perfectly working from our end, Goel! Can you please send your problem to this email: [email protected]?

  12. Hi STACY! Thank you very much for your comment and appreciation. You can follow this article regarding your issue.
    https://www.exceldemy.com/insert-date-picker-in-excel/
    If this doesn’t help, please let us know. You can also send your problem with your Excel file to this email address: [email protected]
    Thanks again for being with us.
    With regards
    -ExcelDemy team

  13. You are welcome, Ruben! Please share your thoughts with us, too. Regards.

  14. Hi Pete, you are right! Variance= ABS(new value-original value)/original value is the correct one.
    We are really grateful for your precious feedback. We have updated the Excel file and main content accordingly.

    Best Regards.
    -ExcelDemy team.

  15. Hi Deepak!
    The following formula can match two criteria and return multiple matches:
    =INDEX($E$2:$E$14, SMALL(IF(COUNTIF($G$2, $C$2:$C$14)*COUNTIF($H$2, $D$2:$D$14), ROW($A$2:$E$14)-MIN(ROW($A$2:$E$14))+1), ROW(A1)), COLUMN(A1))
    Here,
    $E$2:$E$14= Column to return matches
    $G$2 and $H$2= Criteria 1 and 2
    $C$2:$C$14 and $D$2:$D$14= The columns of main data respective to criteria 1 and 2
    In a similar way, you can add any number of criteria.

    Thank you for being with us.

  16. Hi MAHENDRA TRIVEDI! You can use the following formula to compare two columns and return multiple matches in a single cell:
    =TEXTJOIN(",",TRUE,IF(A5:A15=D5,B5:B15,""))
    Here,
    A5:A15= Range for matching criteria
    B5:B15 = Range of the values to return
    D5= Lookup value
    TRUE ignores all the empty cells.

    Check the 3rd case of the following article to know more details.
    https://www.exceldemy.com/index-match-return-multiple-values/

    Thanks for being with us. Best regards.

  17. Hi CLÉMENTINE! You have to select the newly added data from the filter list first (mark the Select All box for that). Then the filter command will work as usual. Thanks!

  18. Hi Stevoisiak! Hope you are doing well.
    Suppose you have the following name in cell B5.
    Mr. Brian Charles Lara
    To extract all the parts of this name, use the following formulas.

    To separate prefix: =LEFT(B5,SEARCH(" ",B5)-1)
    To separate first name: =MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-(SEARCH(" ",B5)+1))

    To separate middle name: =MID(B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1,SEARCH(" ",B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1)-(SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1))
    To separate last name: =RIGHT(B5,LEN(B5)-FIND("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

    You can check the following article too, to know more.
    https://www.exceldemy.com/separate-first-middle-last-name-with-excel-formula/

    Best Regards.

  19. Hi DAVID BAKER! Hope you are well!
    This article will help you to highlight specific data in cells.
    https://www.exceldemy.com/highlight-specific-text-in-excel-cell-vba/
    Best Regards.

  20. Hi GEOFFREY! Your problem is not quite clear to us. However, if you simply copy and paste the cell contents, the hyperlinks will also be pasted. If this is not what you were expecting, then we would request you to elaborate on your issue. You can also send your Excel file to us through email. Thank you!

  21. We appreciate your nice suggestion, ANTHONY! You can also see the following article to know about all the Paste Options in Excel.
    https://www.exceldemy.com/paste-options-in-excel/

    Thank you for being with us. 🙂

  22. Hi Sara, the macro in #9 works fine on our part. Have you changed the Range(B5:D14) according to your dataset? If you have typed the range correctly, and still the macro is not working for the last row, please let us know.

  23. Hi Rakesh! I hope this formula will do the task for you.

    =TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

    Here are more ways to extract just numbers from your data.
    https://www.exceldemy.com/extract-only-numbers-from-excel-cell/

ExcelDemy
Logo