vlookup second value

[aam1932]

How to lookup value for second/third and so on

Example below

Data

Account code sub code
A01 G5
G6
h5

A02 g6
g8
h4

result : current formula i used Vlookup(criterioa,range,column.,true/false)

A01G5
A01G6
A01H5
A02G6
A02G8
A02H4

thank

 

[hippiehacker]

How to lookup value for second/third and so on

Example below

Data

Account code sub code
A01 G5
G6
h5

A02 g6
g8
h4

result : current formula i used Vlookup(criterioa,range,column.,true/false)

A01G5
A01G6
A01H5
A02G6
A02G8
A02H4

thank

Question: Is there a simple way in Excel to VLOOKUP the second match in a column? So, for instance, If I had apple, pear, apple listed in the column (each word in a separate cell), would there be a way to look up the values to the right of the second "apple"?


Answer: This can be done with a formula that utilizes a combination of the Index function, Small function, Row function (all in an array formula).

If you wanted to return the quantity value for the second occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)</pre> When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)}</pre> If you wanted to return the quantity value for the third occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2)</pre> When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2)}</pre> If you wanted to return the bin # for the second occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3)</pre> When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3)}</pre> If you wanted to return the bin # for the third occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),3)</pre> When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),3)}</pre>

 

[chris93]

<tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
[TD="class: votecell, bgcolor: transparent"]

-2down votefavorite
​

[/TD]
[TD="class: postcell, bgcolor: transparent"]I am trying evaluate the following expression in Excel:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2)</code></pre>If <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">apple</code> is contained within the array, the if condition would return

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">ROW(A2:C6)-ROW(A2)+1</code></pre>and

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">ROW(A2:C6) = 2 </code></pre>so

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">ROW(A2:C6)-ROW(A2)+1 = 2-2+1 = 1 </code></pre>If the condition evaluates to False, it would return

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">ROW(C6)+1 = 6+1 = 7 </code></pre>

[/TD]

</tbody>