Index and Match vertically and horizontally

[GinnyG]

Hi,

I am trying to create a formula that lets me match horizontally and vertically.

I want to be able to find the price of a product code: Q6.15410 in the pricing table taking into account it's pricing date and whether it was a regional or metro sale.

Thank you for helping me.

Ginny

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you give us that sample data again in a form that we can copy to test with?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[swapnilk]

Hi,

I am trying to create a formula that lets me match horizontally and vertically.

I want to be able to find the price of a product code: Q6.15410 in the pricing table taking into account it's pricing date and whether it was a regional or metro sale.

Thank you for helping me.

Ginny

Use the undernoted formula at D9

=INDEX($I$8:$L$12,MATCH($A9,$H$8:$H$12,0),MATCH(CONCATENATE(TEXT($B9, "dd/mm/yyyy"),C9),$I$6:$L$6,0))

 

[GinnyG]

Thank you!!