Complex Excel Issue

choyaa

Hi,


I've a little query on Excel that I am not quite sure as to how to implement. I'm not sure if it's possible as it seems too complex.


On a cover sheet I have a list of unique references which are also referenced on "tab2". On "tab2" there is a column with dates in the format "May-01-2016", I need the reference to return the value in cell G of "tab2" if the year relates to 2016 and if it's any other year return the value from column "H".


How can I go about this?

bod1467

You'd be better served if the dates were in a proper date format. That said ...

You get the year by using RIGHT(DateCell,4)

You use an IF to determine if the value is 2016; if it is you use a VLOOKUP to return the column G value, else a VLOOKUP returns the column H value

You need to nest these

=IF(RIGHT(A30,4)="2016",VLOOKUP(To return column G),VLOOKUP(To return column H))

choyaa

I'm currently using the following but it is returning the value "FALSE". Where have I went wrong?


=IF(RIGHT('tab2'!G:G)="2016",VLOOKUP(A30,'tab2'!A:IY,MATCH(tab1!$P$29,'tab2'!$1:$1,0),0))

Cotta

Is there a vloolup missing? Not 100% sure but someone will be able to advise.

rmg1

choyaa wrote: »

I'm currently using the following but it is returning the value "FALSE". Where have I went wrong?


=IF(RIGHT('tab2'!G:G)="2016",VLOOKUP(A30,'tab2'!A:IY,MATCH(tab1!$P$29,'tab2'!$1:$1,0),0))

You've only got the first part of the IF statement (i.e. what to do if the statement is TRUE). Just before your last close bracket, you need a comma and then what to do if it isn't 2016.

choyaa

Thanks, tried the following but it still doesn't work:


=IF(RIGHT('tab2'!G:G)="2016",VLOOKUP(A30,'tab2'!A:IY,MATCH(Tab1!$P$29,'Tab2'!$1:$1,0),0),Match(P28,'Tab2'!'Tab2'!$1:$1,0))

bod1467

RIGHT('tab2'!G:G)

Why are you trying to get the contents of a COLUMN? The RIGHT command only works on a CELL. Also you've missed the comma followed by the number of characters required.

RIGHT('tab2'G10,4) ... would look at cell G10 in worksheet tab2 and return the right-hand 4 characters. You'd need to copy/paste this down the column for all the rows you want to show results for.

choyaa

Thanks Bod, I've mad that adjustment but it is still returning the "False" value. Below is my exact code:


=IF(RIGHT('tab2'!G2,4)="2016",VLOOKUP(A30,'tab2'!A:IY,MATCH(P29,'tab2'!1:1,0),MATCH(Tab1!P28,'tab2'!1:1,0)))

bod1467

You don't have matching statements in the IF command; one has a VLOOKUP with a MATCH, the other just has a MATCH. And even then, the comma is in the wrong place ... you shouldn't be ending the formula with )))

Back to basics ...

Create a VLOOKUP/MATCH statement that returns the value you want for 2016 - TEST IT!

Create a VLOOKUP/MATCH statement that returns the value you want for not 2016 - TEST IT!

Once you know you have working VLOOKUP/MATCH statements then build the total formula.

It's all quite straightforward if you break it down and think logically. Have you read the help documentation for the various commands yet?

IF(Logical Test , Value if True , Value if False) ... the IF command can't be any simpler to understand!

choyaa

Adding the If statement on its own to return a simple "yes" or "no" is fine, however taking it to the next stage of using a v-lookup and match to return specific values is causing headaches.

bod1467

I can't make it any easier - I've already explained how to get the right formula.

=IF(RIGHT(Cell,4)="2016",MATCH(that returns Column G),MATCH(that returns Column H))