IF and VLOOKUP works and then stops

[Kimmmico]

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.

[#2 · Accepted solution]

@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.

[#3]

@OliverScheurichThe first option worked. Thank you so so much I've a new skill for life now :)