IF formula help populate other fields

Keithheavey

New Member
Joined
Feb 4, 2018
Messages
38
Hi,

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

Basically I have a sheet like this:

Post NameAnnounce Desc.File codeAudio file code
Station 1Station 1_welcome112B76
Station 2Station 2_welcome + Dept.1009Y677

<tbody>
</tbody>

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

Well-known Member
Joined
May 10, 2013
Messages
533
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.
 

Keithheavey

New Member
Joined
Feb 4, 2018
Messages
38
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

Well-known Member
Joined
May 10, 2013
Messages
533
Try this, if I am understanding your issue correctly:

Code:
[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.
 

Keithheavey

New Member
Joined
Feb 4, 2018
Messages
38
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 :mad::mad: This seems to have worked, I am going to work away at this now and hopefully it works, I will report back Philwojo :)
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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.
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top