Return certain words in certain cells in order based on range values

[bakarken]

Hi guys


This is quite a confusing question, so I hope I can word it so that you understand.


I am using Excel 2010, and I am looking for an Excel formula (not VBA) to do the following.


On row 1, I have words in columns A - E in a certain order which for this example we can say are


A1: "Excel1"
B1: "Excel2"
C1: "Excel3"
D1: "Excel4"
E1: "Excel 5"


On 'Sheet2', there is a list of data where in column A there is a variety of entries which the user types. Any entry in the A section of 'Sheet2' must be one of the five options on the top row of 'Sheet1' (Excel1, Excel2 etc.)


I am looking for a formula to enter into 'Sheet1' row TWO (A2:E2) that returns the left-most option from A1:E1 IF it appears in the range in 'Sheet2'


So if the user enters 'Excel5' into the column for example, cell A2 will say 'Excel5' as this is the left most option.


If the user enters 'Excel2' and 'Excel3' into the column, this would return 'Excel2' into A2 and 'Excel3' into B2


If the user had all five options somewhere in Sheet2!A:A, A2 to E2 would show exactly what A1 to E1 would show but this may not always happen.


I think I need to use something like =if(countif('Sheet2'!A:A,"Excel1)>0,"Excel1",if(countif('Sheet2'!A:A,"Excel2")>0,"Excel 2"


etc, but I am struggling Can anyone help?

 

[Eric W]

Try putting this in Sheet1, A2:

=IFERROR(INDEX(Sheet2!$A$1:$A$100,AGGREGATE(15,3,MATCH($A$1:$E$1,Sheet2!$A$1:$A$100,0),COLUMNS($A2:A2))),"")
confirm with Control+Shift+Enter. Then drag to the right.

 

[bakarken]

Hi Eric

Thanks so much for the reply, but that does not seem to be displaying them in the correct order.

If A1:A100 said Excel3, Excel4, Excel1, they would be shown in this order but what I want is Excel1, Excel3 and Excel4, if that makes sense?

Thank you

 

[Eric W]

OK, give this one a try:

=IFERROR(INDEX($1:$1,SMALL(IF(ISNUMBER(MATCH($A$1:$E$1,Sheet2!$A$1:$A$100,0)),COLUMN($A$1:$E$1)),COLUMNS($A$2:A$2))),"")
confirm with Control+Shift+Enter.

 

[bakarken]

Hi Eric

That appears to have worked very well, thank you!

Whilst trying to translate this example formula into my actual data, I am also struggling to insert the formula as if two different sheets were being searched for data.

So in my first example, 'Sheet2'!A1:A100 was the range of search, but what if I wanted to look through both 'Sheet2'!A1:A100 and 'Sheet3'!A1:A100 and place the categories in order considering both sheets of data, is that possible?

 

[Eric W]

Try this:

=IFERROR(INDEX($1:$1,SMALL(IF(ISNUMBER(MATCH($A$1:$E$1,Sheet2!$A$1:$A$100,0))+ISNUMBER(MATCH($A$1:$E$1,Sheet3!$A$1:$A$100,0)),COLUMN($A$1:$E$1)),COLUMNS($A$2:A$2))),"")

with Control+Shift+Enter