Discussion Forum
1

# Syn Kaitan

### Posted Questions

No Question(s) posted yet!

Supposing you have two columns with Products and quantities as below screenshot shown. For quickly finding out the quantities of the first or second banana, what would you do?

Please do as follows to find the first, 2nd or nth match value in Excel.

1. In cell D1, enter the criteria which you want to vlookup, here I enter Banana.

2. Here we will find the first match value of banana. Select a blank cell such as E2, copy and paste formula =INDEX(\$B\$2:\$B\$6,MATCH(TRUE,EXACT(\$D\$1,\$A\$2:\$A\$6),0)) into the Formula Bar, and then press Ctrl + Shift + Enter keys simultaneously.

Note: In this formula, \$B\$2:\$B\$6 is the range of the matching values; \$A\$2:\$A\$6 is the range with all the criteria for vlookup; \$D\$1 is the cell containing the specified vlookup criteria.

Then you will get the first match value of banana in cell E2. With this formula, you can only get the first corresponding value based on your criteria.

To get any nth relative values, you can apply the following formula: =INDEX(\$B\$2:\$B\$6,SMALL(IF(\$D\$1=\$A\$2:\$A\$6,ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),1)) + Ctrl + Shift + Enter keys together, this formula will return the first matched value.

Notes:

1. To find the second match value, please change the above formula to =INDEX(\$B\$2:\$B\$6,SMALL(IF(\$D\$1=\$A\$2:\$A\$6,ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),2)), and then press Ctrl + Shift + Enter keys simultaneously. See screenshot:

2. The last number in the above formula means the nth match value of the vlookup criteria. If you change it to 3, it will get the third match value, and change to n, the nth match value will be found out.

You can easily find the first match value in Excel without remembering formulas with the Look for a value in list formula formula of Kutools for Excel.

1. Select a cell for locating the first matching value (says cell E2), and then click Kutools > Formula Helper > Formula Helper. See screenshot:

3. In the Formula Helper dialog box, please configure as follows:

Now the corresponding cell value will be auto-populated in cell C10 based on drop-down list selection.

Answer is posted for the following question.

How to vlookup second column?

British company J.W. Foster and Sons designed and produced the first shoes designed for running in 1895; the shoes were spiked to allow for greater traction and speed.

Answer is posted for the following question.

When was the athletic shoes made?

It has been found that Marigold has the ability to promote the growth of healthy new tissue, increase blood flow to the affected area, boost collagen production (which firms and strengthens skin), hydrate dry skin and speed up the process of skin repair following surgery or damage.

Answer is posted for the following question.

What are the benefits of marigold?

Georgetown University was founded by former Jesuits in the tradition of Ignatius of Loyola; it is a member of the Association of Jesuit Colleges and Universities. Georgetown is not a pontifical university, though seven Jesuits serve on the thirty-six member Board of Directors, the university's governing body.

Answer is posted for the following question.

Who owns georgetown university?