I have a IF(And statement which is not returning the results. It's giving me n/a on column D. I want it to look at column A and column AA and return column W in column D. Any help would be appreciated.
I have a IF(And statement which is not returning the results. It's giving me n/a on column D. I want it to look at column A and column AA and return column W in column D. Any help would be appreciated.
Could you give us a few expected results?
BSB
yes, Column D should return the Durbin pavillion Paint from Column W.
Does this in D2 and copied down help?
Formula:Please Login or Register to view this content.
BSB
Still gives me n/a
Are your dates in column A definitely in date format?
When I open the file they're text rather than dates.
If you expand the width of column A are the dates to the left or right?
BSB
They are dates, I even just reformatted all of them. They dates are to the left and so are the ones in column V.
Can you highlight all of column A then select "Data / Text to Columns / Finish" and see if that changes the results in D?
BSB
It didn't work
OK, then I'm unable to help further. I'm in the UK and your data shows up on my regional settings differently to how it does on yours.
You'll need someone with different regional settings to mine to check if for you I'm afraid.
BSB
I agree with BSB in that col A is text not dates.
If you select col A & format as general you should see numbers like 43423. If you don't then they are not dates.
Use text to columns > delimited > clear all check marks > select date & MDY> Finish.
Hi "T",
If you change the date format in column A to the same as column V your formula works.
Regards
peterrc
It is only working on the first 2 lines but not the rest of them. I reuploaded the file.
That wasn't the suggested formula, which was:
=IF(AND($V2>=A2, $AA2=B2), $W2,"n/a")
Note: you only refer to one row, not multiple.
Remember what the dormouse said
Feed your head
I need it for all the rows with the crew names in it. I used that formula and expanded it.
That won't work. If the formula needs to do more than just look at A and AA in the same row, you'll need some sort of lookup formula. Something like:
=IFERROR(LOOKUP(2,1/(A2=$V$2:$V$59)/(B2=$AA$2:$AA$59),$W$2:$W$59),"No match")
Is this what you want
=INDEX($W$2:$W$59,MATCH(1,($V$2:$V$59=A2)*($AA$2:$AA$59=B2),0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks