LookUp and If Then working together.

[EBoudreau]

I need help getting a formula to work properly in a cell on a different sheet.

Here's what I need to do.
I need the cell to Look in range 'Frac Report'!E8:CQ8 for the entry "LS" or "LSC" and return the value in the matching cell in row 6.
Also if there is no data entered in range 'Frac Report'!E8:CQ8, I want "0" to be returned in the cell.

I've tried this formula:

=IF('Frac Report'!$E$8:$CQ$8="LS",MATCH("LS",'Frac Report'!$E$8:$CQ$8,6),MATCH("LSC",'Frac Report'!$E$8:$CQ$8,6))

but it yelds #N/A in the cell and I cannot have that.

I know there is a formula that will do this, but I am unable to trouble shoot how to get there from the existing broken formula I have already.

Any help would greatly appreciated!

 

[EBoudreau]

It should only happen once if the user enters the data correctly. If LS is entered, then it should trigger the calculation. If the user fills out correctly, then they will change LS to LSC which should then trigger the calculation.

 

[dreid1011]

In that case, let's go back to the formula I suggested earlier. I don't know why, but it's working now after I cleared I3 and re-entered the formula from scratch.

=IF('Frac Report'!$E$8:$CQ$8="LS",INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LS",'Frac Report'!$E$8:$CQ$8,0)),INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LSC",'Frac Report'!$E$8:$CQ$8,0)))

As I'm typing this up, the formula is behaving very odd. It's working one moment, then not the next.

 

[EBoudreau]

That's strange. Every time I try it I always get the #N/A.
I would like it to not show #N/A if LS or LSC are not entered in the range.

 

[dreid1011]

That's strange. Every time I try it I always get the #N/A.
I would like it to not show #N/A if LS or LSC are not entered in the range.

That part is easy. Either way, I've noticed it's working for specific cells (try putting LS or LSC into I5), and I'm not sure why. More tinkering is needed!

But, I've shorted the formula a bit as well:

=IFERROR(INDEX('Frac Report'!$E$6:$CQ$6,,MATCH(IF('Frac Report'!$E$8:$CQ$8="LS","LS","LSC"),'Frac Report'!$E$8:$CQ$8,0)),"")

 

[dreid1011]

The IF is evaluating to False so it's constantly looking for LSC... but it works for LS if it's in cell 5.

 

[dreid1011]

Ok, we have a winner:

=IFERROR(IF(COUNTIF('Frac Report'!$E$8:$CQ$8,"LS")>0,INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LS",'Frac Report'!$E$8:$CQ$8,0)),INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LSC",'Frac Report'!$E$8:$CQ$8,0))),"")

Give this bad boy a go.

 

[EBoudreau]

How many times could LS or LSC appear in the row?

Sorry this took so long to get back to. I got really busy with work. If you wouldn't mind revisiting this topic, ideally LS should happen once in each row of the first tab, which will then be replaced with LSC once in each row.

 

[EBoudreau]

Ok, we have a winner:

=IFERROR(IF(COUNTIF('Frac Report'!$E$8:$CQ$8,"LS")>0,INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LS",'Frac Report'!$E$8:$CQ$8,0)),INDEX('Frac Report'!$E$6:$CQ$6,,MATCH("LSC",'Frac Report'!$E$8:$CQ$8,0))),"")

Give this bad boy a go.

It would appear that this formula is working as i just tried it out. Let me try this in all areas of the spreadsheet where I need it to work.