# look up with multiple vertical & horizontal criteria

#### apn3a

##### New Member
hi,

so i am trying to create a formula where i can look up for a value by using multiple criteria. i want also to get the closest approximation value if one of the criteria is not met.

So my Lookup table is this:

E F G H I J K K M
 Day Name Intervals Week 15 Week 16 Week 17 Week 18 Week 19 Week 20 Wed John 21:00 14.8 17 18.3 19 21.4 22.1 Wed Maria 11:00 14.8 17 4.8 4 4.6 4.5 Mon Stacey 23:00 14.8 17 3 3.4 3.4 4.2 Wed Howard 14:00 14.8 17 3 3.4 3.4 4.2 Wed Howard 23:00 14.8 17 3 3.4 3.8 4.2

<tbody>
</tbody>

<tbody>
</tbody>

and the table i want to return values to (Column E) is this:

A B C D E
 Name Time Slot Week Day Value John 10:00 Week 19 Wed Maria 11:00 Week 19 Wed Alissa 11:00 Week 19 Thu Howard 13:00 Week 19 Wed Stacey 13:00 Week 20 Mon

<tbody>
</tbody>

What i want is to return the "Value" from the above table based on the criteria: Name Match, Timeslot Match (closest approximation if not exact value), Week Match and Day Match.

the sumproduct formula works great, but its not suitable for finding approximations.

the index match formula returns me either a wrong value or a #ref error (Ctrl+Shift+Enter) =INDEX(\$H\$2:\$M\$6,MATCH(C2,\$H\$1:\$M\$1,0),MATCH(1,(A2=\$F\$2:\$F\$6)*(B2<=\$G\$2:\$G\$6)*(D2=\$E\$2:\$E\$6),0))

in E1 i expect to see 21.4
in E2 i expect to see 4.6
in E3 i expect to see nothing (0)
in E4 i expect to see 3.4

etc

Thanks!

Last edited:

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

anyone?

Replies
4
Views
446
Replies
2
Views
201
Replies
6
Views
686
Replies
1
Views
290
Replies
4
Views
811

1,195,669
Messages
6,011,061
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back