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 with current pricing (rate/s). Prices change so additional columns have been entered with the revised dates and updated prices.
2.Register;
This is where after inputting the date ($B), I then select from a drop-down box $C an item/product from the aforementioned sheet, the rate and unit then populate across the row.
This is all working fine.
Seeking to understand the following, when product/item prices change, I have entered a new column on sheet 1 with the revised rates and respective dates.
Is there any way that upon entering the date in "Register $B" that part of Excels interrogation is to ensure that the rate selected is from a rates column not dated older than the date initially entered in $B ?
I have attached a stripped-down workbook with an attempt at explaining my query.
Appreciate and help/feedback or additional information
2 Replies
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) )In G2:
=IFERROR(INDEX(PRICELIST!$D$3:$F$5,MATCH(C2,PRICELIST!$A$3:$A$5,0),MATCH(B2,PRICELIST!$D$2:$F$2)),"")
Fill down.
