Vlookup and If statements

[Cavesim]

Hi All,

I will try and explain my problem and hope you can help.

I need to identify data on one table using a vlookup, if that data contains certain text (ABC) i need it to then get the cell to display data from another cell within the same table.


Below are fragments of the data set

Main Table where data needs to be displayed:

Missing Field is ABC Contract Expiry Date (ABC is a service line)

I use a vlookup to reference customer name (Australia)

Customer	ABC Spend	ABC Share Of Wallet	ABC Product Numbers Sold		ABC Contract Expiry Date
Australia	$ 508,170.45	100%	48,406		Missing Data

<tbody>
</tbody>

I have another table which holds the contract information

Company	Group Code	Contract	Status	Contract Start Date	Contract End Date
Australia	AUS	ABC;ZBA;	Active	18/09/2008	17/09/2011

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I need to check the contract cell to see if it contains "ABC" and if it does then show the contract end date where the first table says missing data.

Hopefully the above is clear.

 

[AlexandraT]

If the first table is in sheet 1 and the second table is in sheet 2 columns A to F, type this in the ABC Contract Expiry Date cell:

=INDEX(Sheet2!F:F,MATCH(1,(IF(ISNUMBER(SEARCH("ABC",Sheet2!C:C)),1,0))*(Sheet2!A:A=Sheet1!A2),0))

In sheet 2:
F is the column which contains the contract end date
C is the column with Contract
A is the column with the Company
In sheet 1:
C2 is the Customer column from the first table.

Hope this was clear enough
Alex

 

[Cavesim]

Hi Alexander - Thought it worked but it gives me an NA# value.

I will try and play around with the formula now that i have seen how you have structured it.

Thanks again for giving it a go and helping me with my problem

 

[Cavesim]

Hi Alexander,

When i look at the calculation steps it all works well bar the this component

=INDEX(Sheet2!F:F,MATCH which equals NA#

 

[AlexandraT]

Try confirming the formula by simultaneously pressing Ctrl+Shift+Enter not just enter. Forgot to mention that. Sorry

Alex

 

[Cavesim]

Works perfectly thankyou

 

[Cavesim]

If i wanted to remove the NA# where would i insert the If statement?

 

[AlexandraT]

=IFERROR(INDEX(Sheet2!F:F,MATCH(1,(IF(ISNUMBER(SEARCH("ABC",Sheet2!C:C)),1,0))*(Sheet2!A:A=Sheet1!A2),0)),"No NA")

A normal iferror will suffice. You can put in any text or any cell/formula at the end where I wrote "No NA". If you want something other than text do not add the apostrophe "".

Alex