Copy cells based on matching index numbers on excel?

Question

Table to copy cells from

Table to paste cells to

The idea is to take the 2 cells in front of the one with index 3 in the first table and paste them in the second table where the indexes match. Since I have over 100 indexes and this process will likely be repeated in the future, how can I make this automatically?

Answer 1

As a unique index is used, the XLOOKUP function can also be used.
Value1 and Value2 can be returned by spilling the results from a single formula.

The formula would look like this:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

With an example of the formula based on your data looking like this:

=XLOOKUP($F5,$A$2:$A$35,$B$2:$C$35,"")

The formula is entered into cell G6 in the example and then copied down only the Value1 column.
Column G in the example screenshot.
Because the return_array references both the Value1 and Value2 columns (Columns B & C), it will return both values by spilling the results from the formula cell into the adjacent cell on the right.

If there is no matching data, an empty string is returned.

Answer 2

Type this formula in cell G4.

=INDEX(B$1:B$13,MATCH($F4,$A$1:$A$13,0),1)

where change the value 13 (both) to the last row value of the source table .

Then copy and paste cell G4 in the G and H column to the end of the target table.