Forum Discussion

Kimmmico's avatar
Kimmmico
Copper Contributor
Aug 16, 2022
Solved

IF and VLOOKUP works and then stops

Hi,

 

I'm not an excel expert so forgive me if this is a silly question or I'm approaching it all wrong.

 

 

I want to pull metric 1 from a range named 'METRIC' only if B2 is the same as any cell in the column 'metric_WK' and only if the first column of the range METRIC is equal to A2. I've entered the range using ctrl + shift + enter.

 

I get the correct result for WK 30 but not for WK 29 or another other WK. I've tried rearranging the order of the column 'metric_WK' that's in the range 'METRIC' but it doesn't make a difference.

 

Is there a way to get this working or is there another formula I can use?

 

I don't have Excel 365 so can't use XLOOKUP.

 

My objective:

 

If the raw data in the range 'METRIC' has 'Beyonce' in in the first column and WK 30 in the 20th column then show me what's in the 19th column.

 

I've spent hours on this so any help would be very much appreciated.

 

Thanks in advance.

Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Aug 16, 2022

    Kimmmico 

    =VLOOKUP(A2&B2,CHOOSE({1,2},Tabelle2!$A$2:$A$7&Tabelle2!$T$2:$T$7,Tabelle2!$S$2:$S$7),2,0)

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

    =INDEX(Tabelle2!$S$2:$S$7,MATCH(1,INDEX((A2=Tabelle2!$A$2:$A$7)*(Tabelle1!B2=Tabelle2!$T$2:$T$7),0),0))

    An alternative could be INDEX and MATCH.

     

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 16, 2022

    Kimmmico 

    =VLOOKUP(A2&B2,CHOOSE({1,2},Tabelle2!$A$2:$A$7&Tabelle2!$T$2:$T$7,Tabelle2!$S$2:$S$7),2,0)

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

    =INDEX(Tabelle2!$S$2:$S$7,MATCH(1,INDEX((A2=Tabelle2!$A$2:$A$7)*(Tabelle1!B2=Tabelle2!$T$2:$T$7),0),0))

    An alternative could be INDEX and MATCH.

     

Resources