Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Apr 24, 2023
Solved

INDEX, MATCH with Combo-box

Hi Experts,                Need a help, in Sheet1, I want to create a formula in B8 such that if in B13 (channel Bandwidth-chbw) is set to 10 then RB should be 24 and so on from another sheet(Sheet2...
Formulas and Functions
  • rzaneti's avatar
    rzaneti
    Apr 24, 2023

    Hi anupambit1797 ,

     

    Please, check if it works for you. Note that I removed your select list and just added a Data Validation containing the list of CHBW from Sheet2, just to make the connection with B13 easier (let me know if this change is a possibility for your use). 

     

    The formula is a VLOOKUP, but I added an IFERROR just to prevent a "#N/A" result if you let the cell empty.

     

    =IFERROR(VLOOKUP(B13, Sheet2!A2:B11, 2, FALSE), "")

     

    Let me know if it helps or if you need any changes on the file.

rzaneti's avatar
rzaneti
Iron Contributor
Apr 24, 2023

Hi anupambit1797 ,

 

Please, check if it works for you. Note that I removed your select list and just added a Data Validation containing the list of CHBW from Sheet2, just to make the connection with B13 easier (let me know if this change is a possibility for your use). 

 

The formula is a VLOOKUP, but I added an IFERROR just to prevent a "#N/A" result if you let the cell empty.

 

=IFERROR(VLOOKUP(B13, Sheet2!A2:B11, 2, FALSE), "")

 

Let me know if it helps or if you need any changes on the file.

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Apr 24, 2023
    Thanks rzaneti, yes it works 🙂
    Also, is it possible to use OFFSET function in B8 to achieve this?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 26, 2023

      anupambit1797 

      I wouldn't recommend using OFFSET for this, since it is a volatile function. But if you must:

       

      =IFERROR(OFFSET(Sheet2!B1,MATCH(B13,Sheet2!A2:A11,0),0),"")

       

      Better would be

       

      =IFERROR(INDEX(Sheet2!B2:B11,MATCH(B13,Sheet2!A2:A11,0)),"")

Resources