Vlookup array formula in a table

Copper Contributor

Hi, is there any possibility to use VLOOKUP and match array functions to auto-populate the table by entering a formula in H2?Screenshot 2024-05-22 011508.png

2 Replies


In H2:

=IFERROR(INDEX($C$2:$E$5, MATCH($G2, $B$2:$B$5, 0), MATCH(H$1, $C$1:$E$1, 0)), "")

Fill down to row 3 and to the right to column J


I assume from your choice of functions that you are not using Excel 365?  If you were, the solution might be expressed in the form

= FILTER(values, COUNTIFS(required,row_hdr))

The formula, placed in the top-left cell of the intended output range, spills to provide the entire array of results without copying down or to the right.