Here is an example using tables on two different sheets. I use MINIFS to do multi-criteria lookups.
Sheet 2::Table 1 starts out with columns for first, last, street, and town. Each name appears once and only once.
Add a column to the right.
Each cell in this new column gets the formula =ROW()
Sheet 1::Table 1 starts with columns A-C (first, last, and service date). The other columns you see in the screenshot below were added to bring in data from the other table.
Add columns for the data you want from the second table (Street and Town).
Add one more column that is a "working" column to do the lookups.
Formula in F2 =MINIFS(Sheet 2::Table 1::E,Sheet 2::Table 1::A,A,Sheet 2::Table 1::B,B)
Fill down
Formula in D2 =INDEX(Sheet 2::Table 1::C,$F2)
If the columns are in the same order as in the other table (like they are in my table) you can drag to the right to do column E and drag down to complete those columns
Note that if a name is listed more than once in Sheet 2, the data will be returned from the first one that matches. If you use MAXIFS, it will return the last one that matches.
