Skip to content

Tag: Excel

Excel Search by Multiple Criteria

If you have a list of horizontal data and you want to turn it into vertical data then you need to use the index() function. As in the example below.

First, prepare a list of two non-repeating values, in my case “Nazwa” and “Kwota”. Then a list of recurring values to help with the search, I search by the “Etap” column.

The formula in the “Adres” column looks like this:

=WYSZUKAJ.PIONOWO(H2;A2:F10;2;0)
=VLOOKUP(H2;A2:F10;2;0)

Nothing special. The real magic begins after this column. Index() function supported by Match().

=INDEKS($A$2:$F$10;PODAJ.POZYCJ─ś($H2&K$1&$I2;$A$2:$A$10&$C$2:$C$10&$F$2:$F$10;0);5)
=INDEX($A$2:$F$10;MATCH($H2&K$1&$I2;$A$2:$A$10&$C$2:$C$10&$F$2:$F$10;0);5)

Remember to confirm the index() function using the key combination Crtl+Shift+Enter.