excelexcel-formula

Trying to return a COG in Excel for a hearing aid based on which hearing aid is selected

I am trying to update an order tracking spreadsheet for hearing aids. We work with 6 different manufacturers who have both hearing aids and accessories.
Right now, the COG spreadsheet is laid out like this:
[COG spreadsheet](https://i.sstatic.net/M63UmL5p.png)

Where the lists of devices with their cost are separated out by manufacturer and device type.

On the tracking spreadsheet, you select which hearing aid was ordered based on the list of manufacturers. I want it to then pull the COG for the hearing aid that is selected from the COG sheet, but I cannot get it to work. enter image description here

The closest I got was pulling the first value from the appropriate list.

This is the formula I'm trying:

=IF([@[Hearing Aid]]<>"",OFFSET(INDEX(tbl_choices,0,MATCH([@[Hearing Aid]],tbl_choices,0)),0,1,1),0)

The formula I used that just pulls the first COG in the list is this (it's pulling the COG for the accessory instead of the hearing aid):

=IF([@Accessory]<>"",(OFFSET(INDEX(tbl_choices,0,MATCH(INDEX(tbl_Orders[@],2),tbl_choices[#Headers],0)),0,3,1)),0)

Any advice is appreciated!

Solution

  • Using this basic version of your COG table:
    enter image description here

    To return this table:
    enter image description here

    =LET(Position, XMATCH([@Manufacturer],tblChoices[#Headers]),
     Product, INDEX(tblChoices,,Position):INDEX(tblChoices,,Position+1),
     FILTER(FILTER(Product,INDEX(Product,,1)=[@[Hearing Aid]]),{0,1}))

    Broken down
    Return the column number of the manufacturer:
    XMATCH([@Manufacturer],tblChoices[#Headers])

    Return all the products and prices for that manufacturer:
    INDEX(tblChoices,,Position):INDEX(tblChoices,,Position+1)

    Filter to the product we're after:
    FILTER(Product,INDEX(Product,,1)=[@[Hearing Aid]])

    Filter the previous filter to show just the second column:
    FILTER(<previous filter>, {0,1}) or you could use INDEX(<previous filter>,,2).

    But, what I'd probably do is have my COG table set out like below:
    enter image description here

    My tracking table would look like this:
    enter image description here

    The COG column containing this formula:

    =XLOOKUP(1,(tblChoices[Manufacturer]=[@Manufacturer])*(tblChoices[Type]=[@Type]),tblChoices[COG],"Item not found")