Forum Discussion

DerekK1590's avatar
DerekK1590
Copper Contributor
Nov 03, 2024

Formula help

Good evening

I need a formula to get item costs from a vender list. I have 2 tabs, in the first tab "worksheet", the second tab is "venders", with the items and prices. On the worksheet tab I created a drop down list with the venders names in cell A3, and a second drop down list in cell B3 with the names of the items. when I select a vender and an item I want cell C4 to show the cost of that item from that vender.

this is the formula I tried to use

(=XLOOKUP(A3,venders!A3:A14,'work sheet'!B3,venders!B4:B14,venders!C4:C14), below is the result.

any help is appreciated.

 

 

    
vender aitem#VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 
  #VALUE! 

 

Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 03, 2024

    Hi DerekK1590 

     

    Your setup isn't top clear to me so this might not be exactly what you want. In attached file you'll see I formatted data as Table (in sheet 'Venders'). I would recommend you to adopt it as Tables bring a lot of benefits and make formulas much more easy to construct and to read

     

    With the Table with the Vendors, Items & Prices, named 'VendersCost':

    in C4:

    =IF( COUNTA( A3:B3 ) = 2,
  FILTER( VendersCost[price],
    ( VendersCost[vender] = A3 ) * ( VendersCost[item] = B3 ),
    "No match"
  ),
  ""
)

     

Resources