Forum Discussion
EXCEL HELP - VLookup and Hlookup combination
Sorry, possibly a straightforward answer no doubt, not for me as a newby... Refer to the attached Workbook. Two worksheets 1. Pricelist; This is where line items (products) are collated along ...
I went for a more complex solution built on Excel 365.
The search is conducted in two parts, the first of which narrows the search down to a single row of rates. An rate that is blank will then be used to remove the date from the search list so that the final search will go back to the latest relevant price point.
= LET(
productRates, XLOOKUP([@PRODUCT],product, rate),
activeDates, IF(productRates>0, dateSet),
XLOOKUP([@DATE], activeDates, productRates, , -1)
)