Search a table and use the returning value to search another table

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Dates > Search a table and use the returning value to search another table

← Previous post -

Filed in Dates, Excel, Search/Lookup on Sep.12, 2012. Email This article to a Friend

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