Search results

You need to change the formula to take into account that you are using more than 4 columns for the names (which was all the original formula was written for)... So for each column after E you need to repeat the "search" part. START WITH : =INDEX('Sheet2'!A:A,MAX( THEN YOU NEED...

Been thinking.... this only works if you have a manageable number of columns... There is no easy way to do this that I know of if you have an array of company names that is extremely large... The easiest solution would be to build a simple custom formula to do what you want to do... Turn on...

=INDEX('Sheet2'!A:A,MAX(IFERROR(MATCH('Sheet1'!G2,'Sheet2'!B:B,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!C:C,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!D:D,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!E:E,0),0))) So: 1. look for G2 ("Brighton&HA") in Column B and if it isn't there return a zero, else...

HANDY TIP VLOOKUP is vastly over-rated given that it takes more CPU and is less flexible that INDEX+MATCH... So Instead of = VLOOKUP(<SearchCell>,<WholeArray>,<Offset of Search Col>,False) it is better to be in the habit of using... =INDEX(<ReturnCol>,MATCH(<SearchCell>,<SearchCol>,0))...