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!

 

[dreid1011]

It looks like you are using MATCH incorrectly. The last value can only be 1, 0 or -1. INDEX will return the value in row 6 based on the MATCH of row 8. Perhaps this:

=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)))

 

[EBoudreau]

I Tried plugging that in and I still get a return of #N/A.

 

[dreid1011]

I Tried plugging that in and I still get a return of #N/A.

=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)))

Try this one with the extra , in the INDEX functions.

 

[EBoudreau]

No Unfortunately that didn't work either. Same #N/A return.

 

[dreid1011]

Can you post a samples of the 'Frac Report' sheet and the sheet the formula is on?

 

[EBoudreau]

How can i upload my file directly?

 

[dreid1011]

There are some indirect methods, like uploading your file to dropbox.com and posting a link here. Or, you can use the MrExcel HTML Maker (found here: MrExcel.com | Excel Resources | Excel Seminars | Excel Products) to convert all or a portion of your table to html and post it.

 

[EBoudreau]

Here ya go. Let me know if this link for google drive will for you.

https://drive.google.com/open?id=0B0oQEry29JNjenNKSGhaeGJRTEE

Need to download to be able to open workbook.

Description of needs revisited:
I would like cell 'Stage Times'!I3 to look at 'Frac Report'!E8:CQ8 and fill cell with nothing unless there is "LS" or "LSC" typed into one of the cells in that range.
If there is "LS" or "LSC" typed into one of the cells I want the matching cell contents from row 6 entered into the box.

 

[dreid1011]

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