Forum Discussion

RobMac14's avatar
RobMac14
Copper Contributor
Feb 18, 2022
Solved

VLOOKUP, INDEX and MATCH - can't get them to work

Hi, I'm trying to set up an automatic query that when a code is entered a data dump of information is searched to find the risk ratings for that code in 3 different counties.  The data dump will be ...
excel
Formulas and Functions
  • JMB17's avatar
    JMB17
    Feb 18, 2022

    RobMac14 

     

    You could try this, if you don't have office 365, then you'll need to hit Ctrl+Shift+Enter after keying into the formula bar:

     

    =INDEX('County Data Dump'!$H$2:$H$1846, MATCH(1, ('County Data Dump'!$D$2:$D$1849=$B3)*('County Data Dump'!$B$2:$B$1849=C$2), 0))

     

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 19, 2022

RobMac14 

=VLOOKUP($B3&C$2,CHOOSE({1,2},'County Data Dump'!$D2:$D1849&'County Data Dump'!$B2:$B1849,'County Data Dump'!$H2:$H1849),2,0)

This formula with VLOOKUP could be an alternative. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

  • RobMac14's avatar
    RobMac14
    Copper Contributor
    Feb 21, 2022
    Thanks Quadruple_Pawn for your suggestion. CHOOSE is new to me so another new function to explore. Many thanks.

Resources