Forum Discussion

cmckernan1's avatar
cmckernan1
Copper Contributor
Jul 17, 2024

What's my Best way to return a data value - Xlookup or Index/Match ?

What's my Best way to return a data value - 

 

Hello what's my best way to return a value based on multiple criteria? 

 

X lookup or Index/Match ?

 

I have the sample data sheets below 

I need Column 'R' in 'sample data master' to look at column 'g' and column  'd' in  'sample data master' and look into 'Sample data 1 lookup' against column 'd' and 'e' and return column 'h' in  'Sample data 1 lookup'

 

Happy to use any formula that does this - so just asking for help 

excel
Formulas and Functions
training

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 17, 2024

    cmckernan1 

    In addition, you may return data at once

    =XLOOKUP(
  $D$3:INDEX($D:$D, COUNTA($D:$D)+1) & "@" &
  $G$3:INDEX($G:$G, COUNTA($D:$D)+1),
 '[Sample data 1 lookup -.xlsx]Sheet1'!$D$2:$D$100000 & "@" &
 '[Sample data 1 lookup -.xlsx]Sheet1'!$E$2:$E$100000,
 '[Sample data 1 lookup -.xlsx]Sheet1'!$H$2:$H$100000,
 "---" )
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 17, 2024

    cmckernan1 

    In R3:

     

    =IFERROR(INDEX('[Sample data 1 lookup -.xlsx]Sheet1'!$H$2:$H$100000, XMATCH(1, ('[Sample data 1 lookup -.xlsx]Sheet1'!$D$2:$D$100000=D3)*('[Sample data 1 lookup -.xlsx]Sheet1'!$E$2:$E$100000=G3))), "")

     

    or

     

    =XLOOKUP(D3&"|"&G3, '[Sample data 1 lookup -.xlsx]Sheet1'!$D$2:$D$100000&"|"&'[Sample data 1 lookup -.xlsx]Sheet1'!$E$2:$E$100000, '[Sample data 1 lookup -.xlsx]Sheet1'!$H$2:$H$100000, "")

     

    Fill down.

Resources