ExcelBanter - Horizontal and Vertical lookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Horizontal and Vertical lookup (https://www.excelbanter.com/excel-worksheet-functions/445919-horizontal-vertical-lookup.html)

Horizontal and Vertical lookup

Hello, I was wondering if someone could help me complete the following.

I need to look up a values of A2 & B2 in sheet 2 and return the value in sheet 1 D2

Sheet 1 - Column B and C are data validation dropdown list.

A B C D
Date From To # Miles
Burr Holland Hill #N/A

Sheet 2
A B C
Burr Burr 0
Burr Dwight 2.8
Burr Holland Hill 5
Burr Jennings 4.4

Example

A B C D
Date From To # Miles
Burr Holland Hill 5

I tried =INDEX(Sheet2!C1:C4,MATCH(1,IF(Sheet2!A1:A4=C15,IF (Sheet2!B1:B4=F15,1)),0)) using control, shift enter and I end up with N/A in Sheet 1 D2.

Thank you/

Horizontal and Vertical lookup

Hello Lisa,

Am Mon, 30 Apr 2012 13:45:03 +0000 schrieb Lisa M:

Example

A B C D
Date From To # Miles
Burr Holland Hill 5

try:
=INDEX(Sheet2!C1:C100,MATCH(B2&C2,Sheet2!A1:A100&S heet2!B1:B100,0))
Array formula to enter with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you very much for your help! I will give this a try.