IF COLLECT / MATCH COLLECT / INDEX COLLECT / VLOOKUP ?

Michaël @ NDA

Dear Community,

I am trying to have an automatic filling of the column [Budget Max] while changing the column [Level]

based on 2 other columns references: [Level attribution] and [Max Budget Range] with matching ROWS.



If [Level]value = [Level attribution]value return [Max Budget Range]value of the same ROW of [Level attribution]matchingvalue.



This Formula works, but only for one row, while I would like it to work for the all sheet:

=IF(Level@row = [Level attribution]1, [Max Budget Range]1)



I have tried other possibilities below, but all of them returns either #Invalid Column Value or #Unparseable or # Incorrect Argument Set...



=COLLECT([Max Budget Range]1:[Max Budget Range]5, [Level attribution]1:[Level attribution]5, Level@row)



=IF([Level attribution]1:[Level attribution]5 = Level8, [Max Budget Range]@cell, "-")

=IF(Level@row = COLLECT([Level attribution]:[Level attribution], [Max Budget Range]:[Max Budget Range]))

 

Any suggestion would be of great help, thx!

Paul Newcome

Try something along the lines of this...

 

=INDEX($[Max Budget Range]$1:$[Max Budget Range]$5, MATCH($Level@row, $[Level attribution]$1:$[Level attribution]$5, 0))

Michaël @ NDA

Dear Paul,

Thank you very much, that worked beautifully!



I added an IFERROR to avoid a #NOMATCH return:



=IFERROR(INDEX($[Max Budget Range (monthly)]$1:$[Max Budget Range (monthly)]$5, MATCH($Level@row, $[Level attribution]$1:$[Level attribution]$5, 0)), "")



and extended this formula to another sheet for reference:



=IFERROR(INDEX({NDA-VN Strategic Hiring Plan - 2019 Range 1}, MATCH($Level@row, {NDA-VN Strategic Hiring Plan - 2019 Range 2}, 0)), "")



thx for the great help.

Best

Paul Newcome

Happy to help! 

 

Just a little suggestion... When referencing another sheet, I personally try to rename my ranges as "Sheet Name Column Name". So the Projected Complete column in my Rollup sheet as a cross sheet reference would look like {Rollup Projected Complete}. For me personally, it makes trouble shooting and tweaking a lot easier in the long run.