Two dimensional lookup?
I have a sheet called Shipping that contains the standard shipping costs. In the columns, I have product. In the rows, I have destination countries. So each cell tells me the price to send product to country for the (product, country) pair.
I have another sheet that captures live information on orders. Column C has product name, and column G has country name. In column R, I have to provide the shipping cost, based on a lookup of the Shipping sheet.
I am at a loss here, as I am familiar with VLOOKUP and HLOOKUP, but they are uni-dimensional lookups...the number of products/countries is large and dynamic, so I don't want to simply create a separate table to create another lookup for the column/row number to input as index in my original lookup...unless I have to.
Would greatly appreciate any help.
try:
=INDEX(IFNA(VLOOKUP(F2:F&"×"&G2:G, SPLIT(FLATTEN(
IF(B2:D="",,A2:A&"×"&B1:D1&"♦"&B2:D)), "♦"), 2, 0)))
