INDEX MATCH formulas

In the previous videos, we've learned about the usages of INDEX and MATCH formulas, respectively.The INDEX function can return a value in a cell regarding its column and row number in an array. Combining the INDEX MATCH formulas makes it flexible in value lookup. We can break the limitations of VLOOKUP and realize the reverse lookup. Here is a form recording the personal information of staff. How can we look up the 'Number' of a corresponding staff? We can run the INDEX MAT....

Use MATCH function to search location information

The MATCH function can return information about the location of the lookup content based on the lookup value. Take this spreadsheet as an example. We want to find the location of the column where the contact Andrew is located. Then we can use the MATCH function. The syntax structure of MATCH function is: MATCH(lookup_value,lookup_array,match_type)In this table, we need to find out which row Andrew is located in column A by his name in cell D3. Click the Formulas tab and the Insert Func....

How to use the Excel MID function

When using WPS Spreadsheets to process data, sometimes we need to catch part of the data from a series of numbers. We can use the MID formula to realize it, to catch up a certain length of characters from a series of numbers. In this example, there is a list of phone numbers. The MID function can extract the long series of numbers, keeping the last seven numbers when removing the first three characters. Select cell D2 and click the Insert Function to pop up the Search for a function d....

Color schemes: change the theme color with just one click in WPS Presentation

When making WPS presentation files, are you still troubled by not knowing how to match colors between the elements? This Color Schemes feature will help you. Take this slide as an example. Click the Design tab above and then click the Color Schemes button. Here, WPS Office offers multiple color schemes with a sense of design. All we need to do is choose a color scheme that fits the theme of the presentation. Tips: This feature is only valid for elements that are included with the WPS P....

Quickly replace the content and font in WPS Presentation

When using WPS Presentation, we want to make changes to the specified content or font of the text. Take this slide as an example. If we want to quickly replace WPS in the text with WPS Office,Click the Home tab, then click the Replace drop-down button and choose Replace.Or use the shortcut key Ctrl+H.In the pop-up dialog box, enter WPS in the edit box. We can also set the replacement conditions as needed, such as Match case and Find whole words only.In the Find what box, enter WPS. Whi....

How to find the specified text content in WPS Presentation

When using WPS Presentation, how can we quickly find the text in the slide? Take this presentation as an example. If we want to find the WPS in this file quickly, we simply click the Home tab and then click Find. Its shortcut key is Ctrl+F. In the pop-up dialog box, enter WPS in the edit box. We can also check the replacement conditions as needed, such as Match case and Find whole words only.Once we set it up, click Find next to quickly find the specified text content in the presentati....

How can we use the data validation feature

We can greatly improve the accuracy of data entry in our work if we use the data validation feature of WPS Spreadsheet. Take this table as an example. This is the information collection form for US citizens joining tour groups for international travel. We need to fill in the information of all the participants. How can we ensure that the passport numbers and phone numbers we entered will not be wrong? 1. First, select the cell area where data needs to be filled.2. Click the Data tab.3.....

TEXT formula in WPS Spreadsheet

The TEXT formula in Excel is commonly used for the conversion of number-to-text in a certain format, and it could be also used in Microsoft Office Excel and Openoffice. Normally, the TEXT formula is used to customize format cells in terms of actual needs. To realize it, we need to adjust the second parameter of the formula, which stands for format texts to be set. In this example, the number 2.012584, which equips with six decimal places, entered cell A2. How can we convert it into a v....