Nena asks:

Hi Oscar,I've been trying to find the solution for my lookup problem for a while now and you seem like the right person to ask... Your lookup code works great (thanks) but I need to do two or three lookups within identified matching records... in other words:
Sheet 1 - 'File data'
1. client name
2. filename
3. file date create
Sheet 2 - 'Client data'
1. client name
2. client ID
3. service start date
4. service end date
I need to map correct client ID based on lookup by client name and then based on finding which service date range does client file created date fit into.
So I need to:
1. First search - Identify Client records with matching name
2. Second search - Within that range, I need to find fitting date range.
Your lookups are great when I search entire sheets but I need to do second search based on subset of data.
Any help will be much appreciated.
Thanks!
Nena

Answer:

I created two tables containing random data.

Sheet1, Table1

Sheet2, Table2

Sheet 3:

Formula in cell A6:

=INDEX(Table1,MATCH($B$1,Table1[Client name],0),COLUMN(A1))

This formula contains a reference to a table. Dragging the cell by the handle to 'pull' the formula across multiple columns won´t work. Copy cell A6 and paste to cell range B6:C6.

Array formula in cell A9:

=IFERROR(INDEX(Table2, SMALL(IF(($A$6=Table2[Client name])*($C$6<=Table2[Service end date])*($C$6>=Table2[Service start date]), MATCH(ROW(Table2[Client name]), ROW(Table2[Client name]))), ROW(A1)), COLUMN(A1)), "")

How to enter an array formula

  1. Select cell A9
  2. Click in formula bar
  3. Paste above array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Copy cell A9 and paste to cell range B9:D9. Copy cell range A9:D9 and paste to cell range A10:D14.

Recommended blog post:
Search two related tables simultaneously (vba)

Download excel *.xlsx file

Nena.xlsx