Excel Formula help

[Stuart J]

I'm trying to create a IF formuala, so that if the value of A12 equals a word in a particular column (R) , then I want the formula to return a value of the word in cell V2.

Struggling!

 

[Chris F]

are there multiple words in Coloumn R?

 

[Stuart J]

Yes, each cell in the column is a different word.

 

[hollister]

Where are you wanting the result to be output and are you dragging it down the column? And are you only looking for the word in A12?

 

[Jerry]

It should be something like this:

formula need to be in V2

if(a12=“r1:r100,a12,0)

I’d need to check it though when I’m sat at computer and not in a field!!

 

[mezz]

I'm assuming column R contains multiple words and you basically want to check if value in A12 is contained in column R.

No straight forward way to do it. Simplest way is possibly to do a vlookup of the value and see if it returns an error (meaning it's not there). Could also use index function instead of vlookup
=if(iserror(vlookup(A12,R1:R100,1,FALSE)),"",V2)

 

[Stuart J]

It's for a spray record spreadsheet. So when I select Proline from the product list, it then automatically puts 'disease control' into the next cell, and a default rate of 1 l/ha in the next one?

 

[Jerry]

It's for a spray record spreadsheet. So when I select Proline from the product list, it then automatically puts 'disease control' into the next cell, and a default rate of 1 l/ha in the next one?

i would use VLookups in that-case I think.

 

[bitwrx]

Is INDEX, MATCH not the jobby for this? Not at a computer, so can't check, but it's a standard combination of formulas that works where VLOOKUP doesn't.

Google will tell you the rest.

 

[mezz]

Is INDEX, MATCH not the jobby for this? Not at a computer, so can't check, but it's a standard combination of formulas that works where VLOOKUP doesn't.

Google will tell you the rest.

Vlookup will work fine in this case, but index match is preferred, because it is less likely to break if the spreadsheet layout is changed.

 

[bitwrx]

Vlookup will work fine in this case, but index match is preferred, because it is less likely to break if the spreadsheet layout is changed.

Nice to know it's not just me flying the flag for robust spreadsheet design in UK Ag.

 

[Ukjay]

Would this help you - its not overly complicated but then you can easily edit everything - you just need to add something similar to the appropriate cells?

Start with Validating a set of words for column A (I have shown the range in lines 07 to 11):

Then use the IF for cell B2 could be like this:

Then use an IF statement in C2 could be like this:

The result gives this:

 

[badger nadgers]

One list of products and one list of fields (things you already have and don't want to retype every time).
One live sheet that references and looks up those via pick lists
Separates calculations from constants and enables things to be hidden away.

 

[badger nadgers]

Would this help you - its not overly complicated but then you can easily edit everything - you just need to add something similar to the appropriate cells?

Start with Validating a set of words for column A (I have shown the range in lines 07 to 11):

use an IF statement in C2 could be like this:

Agree with validating input, but nested IFs are nature's way of telling you to use a vlookup. And IIRC you can only do 8 levels of nesting them (up to Excel 2010 at least). Reference a named range (rather than a cell range) with a vlookup then if that range changes e.g. a new product or field, then it will accomodate the new line in the looked up data without having to edit the formula.

 

[Bloders]

i think xlookup is more versatile than vlookup?
Ive being doing some excel stuff to compare lists of animals to spot anyy missing animals or duplicates. Similar sort of use in many ways.

 

[Exfarmer]

Have no knowledge of Xlookup or Vlookup so may be talking rubbish, but I think you need a Data base such as Access
it takes a fair bit of work to get into, but after some practice it is amazing what it will do