Having trouble with formula to return value or leave blank.

[pscinc24]

I am looking for help with combining these two formulas to return a answer into one cell. I have used them and they work on there own. I have tried several different ways using the or, and, if statements add into the formula with no luck. Trying to get the formula to look at two different columns on a different spreadsheet. Thanks for any ideas.

=IF(ISBLANK(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,11,FALSE)),"","NA")
=IF(ISBLANK(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,9,FALSE)),"","NA")

 

[jtakw]

Hi,

Not sure I understand exactly, may be one of these:

=IF(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,11,0)="",IF(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,9,0)="","NA",VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,9,0)),VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,11,0))

=IF(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,11,0)="","",IF(VLOOKUP(AQ14,'OT HOURS '!$A$6:$AJ$41,9,0)="","","NA"))

NOTE: you have a "space" after your sheet name 'OT HOURS '!, please make sure that's correct.

 

[pscinc24]

Thanks, but that did not work. What I'm looking for is that those two formula are made into one. With using your answers it didn't give me any answer. And the 'OT HOURs ' does have a space in it. If I take out the isblank statement I get an wrong answer for the cell. What I'm trying to do is on Bid sheet Column AS cells for the name in column AQ then go to 'OT HOURS '. Then using finding the name in column A looking thru column I thru AJ for any valve. If there is return an answer of NA for the cell in bid sheet column AS. Hope this helps

 

[jtakw]

1st, I did not take out the ISBLANK, I just wrote it differently, rather than ISBLANK(....), I wrote ="", which does the same job.
2nd, your BID sheet AQ & AR looks like Merged cells for "Name", whereas, your OT HOURS sheet has Separate "Last", "First" name in Columns A & B.

For better understanding, please post sample with XL2BB, see my signature, and show sample data with expected results, I'm thinking you don't even need VLOOKUP, may be just COUNTIFS.

 

[pscinc24]

I understand your problem with the look of the sheet. It is separate by Last & first just not label on that sheet. I will try to upload xl2bb on my home computer, work keeps locking up. But, for now here is a image of what the formula's look like in there cells. And image of what the same area looks like under normal conditions. Along with a new OT hours with info add into it. What I really trying to do is have the formula look at 2 different columns in the OT hours. Example AL4 looks up on the OT hours and if column 9 which is I is blank leave blank or return the answer on NA as the formula does now. But also look at column 11 which is K with the same thing leave blank or return NA. The formula works but need it to so look at both so I know who can work or not. The OT hours has 0.00 in it because that is the days people are schedule to work .

 

[pscinc24]

Thanks for the help, decided to go a different why to fix the problem. But, your help in a different part of the spreadsheet.

 

[jtakw]

You're welcome, glad you found a way to solve your issue.