Excel formula to look up first instance of a range of words in a range of cells

[Willforth]

Hi, I wonder if anyone can help.

In 4 columns I have a range of 4 letters. These letters will only appear if another cell meets certain criteria that I have programmed otherwise the cell will be blank

Column H ="WS"
Column I ="WL"
Column J= "LS"
Column K = "LL"

The range of the cells I want covered is H4:K:29

Within this range I want a formula that returns a "W" in cell if the first letters it sees in the range are "WS" or "WL". If the first thing it sees is "LS" or"LL" it returns an "S" if it sees nothing, then return a 0

I am now at the stage where my brain is exploding!!

 

[Sthrncali]

Ok im gonna take a stab... However, I am not certain I am following exactly what you want, however I will try my best to explain the formula so you can easily modify it to fit your needs.



=IF(LEFT(A1, 1)="W", "W", IF(LEFT(A1, 1)="L", "S", 0))


Explanation -

Cell A1 refers to the cell you want to test.
Left(A1, 1) tells the formula to extra the first letter from the text and test if that letter is a "W" - If it is the formula terminates with the result of "W".
The next Left(A1, 1)="L" repeats the process, however this time its looking for an "L" instead of a "W", and if that evaluates to TRUE the formula terminates with the result being "S".
Finally if the first letter was neither "W" or "L", the formula returns a result of "0".

Hopefully this is what you were asking for... If not, just post some more details..

 

[Willforth]

Hi, thanks so much for your help. I had to adapt the formula you posted and unfortunately, Excel will not accept it. it says there are too many arguments in the formula.

The formula I wrote was; -

=IF(LEFT(H4,1)="WS","W",IF(LEFT(I4,1)="WL","W",IF(LEFT(J4,1)="LS","L",IF(K4,1)="LL","L",0))

I can see where you were going with this formula and I would be grateful to find out where i am going wrong with this. This formula will return a "W" or "L" or "0" in the column and row where it is inserted. That is great.

However, I will need an additional formula in another column to record a "W","L","0" based on it reading the results of the first formula. Basically, if the first value it sees in a range of 24 cells is "W" in cell 2 return a "W" and ignore the following 22 cells. Or if the formula sees "L" first in cell say 5 return a "L" and ignore the remaining 19. If it first sees a "0" in cell, say 9, return a "0" and ignore remaining 16 cells.

I hope this is clear and I hope you can help me out with this.

ATB

 

[Sthrncali]

Ok well first off, this formula:

=IF(LEFT(H4,1)="WS","W",IF(LEFT(I4,1)="WL","W",IF(LEFT(J4,1)="LS","L",IF(K4,1)="LL","L",0))

The reason Excel is not accepting is you are providing a 2 letter criteria "WS", "WL", "LS", and "LL" --- However =LEFT(x, 1) is indicating to only look at the first letter...

=Left(YourCell, #ofCharactersToReturn)

So =Left("John", 2) for example would result in "Jo"

As written you are asking Excel to evaluate: If "W" = "WS" then, which is why you are getting an error.

As for the second piece, I apologize but I am not certain I am following your logic... Can you please provide some more insight into exactly what the data may look like, and the expected result of the formula given the sample data?

 

[Willforth]

Thanks for your patience with me....Hey, I'm learning!

So, based on your comments as I understood them, I changed the formula from:-

=IF(LEFT(H4,1)="WS","W",IF(LEFT(I4,1)="WL","W",IF(LEFT(J4,1)="LS","L",IF(K4,1)="LL","L",0))

To:-

=IF(LEFT(H4,2)="WS","W",IF(LEFT(I4,2)="WL","W",IF(LEFT(J4,2)="LS","L",IF(K4,2)="LL","L",0))

Excel still threw me out with the statement "Too many arguments". What am I doing wrong?

-------------------------------------------------------------------

As for the second part of my request, look at the image below for examples

I have created 4 dummy tables. The formula I need would look in Row 1 and then scan Column 1, then Column 2, then Column 3, Then Column 4, if the cells are blank it then moves to Row 2 and scans teh columns and so on up to and including Row 24.

If the first value it sees is "WS" or "WL", the result in the top right hand corner is "W" and it will ignore all other values in the remaining 24 rows and 4 columns.

If the first value it sees is "LS" or "LL", the result in the top right hand corner is "L" and it will ignore all other values in the remaining 24 rows and 4 columns.

If there are no values in the table, it records a "0"

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you looking for a result for each row or just a single result for the whole table?

 

[Weaver]

Since you only have the 4 possibles, I'd suggest:

=IF(LEFT(H4,2)="WS","W",IF(LEFT(I4,2)="WL","W",IF(LEFT(J4,2)="LS","L",IF(K4,2)="LL","L",0))

can become:

=if(or(left(h4,1)="W",left(h4,1)="L"),left(h4,1),0)

 

[Peter_SSs]

Weaver

Your first formula does not appear to be a valid formula.
Your second formula only seems to reference one cell so I'm not sure how that will help.


If we were just looking in a single row I think we could use:
=LEFT(H4&I4&J4&K4,1)


Willforth

On reviewing the question I think you are looking for a single formula to consider a rectagular range. See if this is the sort of thing you want.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. It extracts the first letter from the first non "" entry in the first non "" row of the green range.

Perhaps there is a simpler way, but this is all I've been able to come up with so far and would only be any use if you do not have too many columns in your tables.

Excel Workbook
	H	I	J	K
3				W
4
5			WL	LS
6	LS	WL	LL	WS
7		LL		WL
8				WS
9
Extract first letter

 

[Peter_SSs]

Perhaps there is a simpler way, ..

A bit simpler, still array-entered

=LEFT(INDEX(H4:H8&I4:I8&J4:J8&K4:K8,MATCH(TRUE,LEN(H4:H8&I4:I8&J4:J8&K4:K8)>0,0)),1)
or
=LEFT(INDEX(H4:H8&I4:I8&J4:J8&K4:K8,MATCH(TRUE,H4:H8&I4:I8&J4:J8&K4:K8<>"",0)),1)

 

[Peter_SSs]

If there are no values in the table, it records a "0"

I also forgot about that. So, recapping & including that check.

Excel Workbook
	H	I	J	K
3				W
4
5			WL	LS
6	LS	WL	LL	WS
7		LL		WL
8				WS
9
Extract first letter