Hi,
Required a formula help to return a Qty and Ref# Code in (Column C:D) as per lookup criteria Item Code available in column A.
The main data available in column I to X with help of these data return a required results.
Sample Sheet enclosed with the expected results highlighted in yellow color.
Thanks for the help.
As you use Excel 365 my proposition is not to use formulas, but Power Query (which is built in Office365 application).
See ribbin Data, and Queries and connections to see ho data has been unpivoted into green table.
Best Regards,
Kaper
Many thanks kaper for your PQ Solution.
However, Could someone suggest the formula solution for dynamically update the values without clicking refresh.
Thanks,
Maybe this:
=IFERROR(INDEX(INDEX($K$4:$X$8,MATCH(A4,$I$4:$I$8,0),),AGGREGATE(15,6,COLUMN($K$2:$X$2)-COLUMN($K$2)+1/(INDEX($K$4:$X$8,MATCH(A4,$I$4:$I$8,0),)<>""),COUNTIF(A$4:A4,A4))),"")
and this:
=IFERROR(INDEX($K$2:$X$2,AGGREGATE(15,6,COLUMN($K$2:$X$2)-COLUMN($K$2)+1/(INDEX($K$4:$X$8,MATCH(A4,$I$4:$I$8,0),)<>""),COUNTIF(A$4:A4,A4))),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks