We have a list of orders with numbers and names of goods. I would like, for example, to pull out from the table by order number all the goods that are included in it. More or less like this:
Wonderful feature VLOOKUP (VLOOKUP) in such a situation it will only partially help, because is able to extract data only by the first found match, i.e. will only give us Apples. To find and extract all items from the table, it is better to use an array formula. Like this one:
=INDEX($B$2:$B$16;LEAST(IF($E$2=A2: A16;LINE(B2: B16)-1;»»);LINE()-5))
It must be entered as follows:
- select the cells where the results should be displayed (in our example, this is the range D6:D20)
- enter (copy the formula into the first cell) of the range
- press Ctrl + Shift + Enter
Unit subtraction in fragment STRING(B2:B16)-1 is done because of the table header. For the same reason, to compensate for the shift in the resulting range relative to the original one, the number five in the fragment is subtracted STRING()-5
To hide the #NUM! error that will appear in empty cells in the resulting range D6:D20, you can use the error checking functions IF and EOSH, replacing our formula with a slightly more complex one:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
In Excel 2007, a more convenient IFERROR function appeared – it allows you to solve the problem more compactly:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
In the English version of Excel, these functions will look like this:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Using the VLOOKUP Function to Find Data in a Table
- An improved version of the VLOOKUP2 function that can search in any column and not just the first value
- VLOOKUP2 and VLOOKUP3 functions from the PLEX add-on
- What are array formulas and what are they used for?