IF formula help populate other fields

[KiloHotel]

Hi,

I am trying to figure this one out, Hopefully someone can help me.

Basically I have a sheet like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Post Name[/TD]
[TD]Announce Desc.[/TD]
[TD]File code[/TD]
[TD]Audio file code[/TD]
[/TR]
[TR]
[TD]Station 1[/TD]
[TD]Station 1_welcome[/TD]
[TD]112[/TD]
[TD]B76[/TD]
[/TR]
[TR]
[TD]Station 2[/TD]
[TD]Station 2_welcome + Dept.[/TD]
[TD]1009[/TD]
[TD]Y677[/TD]
[/TR]
</tbody>[/TABLE]

I have a separate sheet with all the post names and I am running a VLOOKUP formula for the "Post Names" column but what I need is to have whenever I put e.g Station 1 in to the "A" column that it will automatically populate columns B-D with the correct description, File code & audio file code, I have tried doing VLOOKUP for the other cells but the file is messy and puts in a lot of N/A & Blanks etc.

This sheet is going to cater for a whole route network and it would take months to type manually so hopefully someone has a smart IF formula that I just can't figure out.

Thanks.

 

[philwojo]

Just do a test on your vlookup, use ISERROR, and if it is then put "" or whatever you want if it doesn't find what you are looking for, and then if it does find it (meaning it isn't an error) then do your same vlookup to return the actual values for B-D.

 

[KiloHotel]

Thanks for replying,

I have no real issue with the N/A & Blanks as I can just use the search and replace for that, I have used the VLOOKUP on each of them cells and the issue lies when I drag down the auto populate down and the formula doesn't stick for whatever reason.

The formula used looks like this for Column B : =VLOOKUP(A1,'Announcements by Route'!B3:G5,2,FALSE)

But when I drag down it should follow but it doesn't for whatever reason, this is the formula on the 2nd row for eg. =VLOOKUP(A2,'Announcements by Route'!B4:G6,2,FALSE)

 

[philwojo]

Try this, if I am understanding your issue correctly:

[COLOR=#333333]=if(IsERROR(VLOOKUP(A1,'Announcements by Route'!$B$3:$G$5,2,FALSE)),"",([/COLOR][COLOR=#333333]VLOOKUP(A1,'Announcements by Route'!$B$3:$G$5,2,FALSE)))[/COLOR]

Put that in your first cell, then drag down and see if it works or not. It should also replace the #N/A with just a blank now.

 

[KiloHotel]

Ah! It was the table array! It was changing when I was dragging down and not searching the top lines on the other sheet! I was losing a row of array on each row dragged down This seems to have worked, I am going to work away at this now and hopefully it works, I will report back Philwojo

 

[philwojo]

No problem, I hope it works for you, but yes you just need to lock the range down if you don't want it to change.

Post back if you need more assistance.

 

[KiloHotel]

No problem, I hope it works for you, but yes you just need to lock the range down if you don't want it to change.

Post back if you need more assistance.

Worked a charm! Thanks again