IF function to return text if a cell = another range of cells

**Richie Bell** · 01-09-2015, 10:14 AM

Is it possible to write a formula to return certain words if a cell is equal to other ranges?

For example, I am writing a Bill of materials and I need column G to say "metres", "Each", or "Pair" depending on the hardware used. That info comes from another sheet in the workbook (Master).

The formula I have written works for simple one cell equals another cell, but if cell Master!C118 equals a cell in the described ranges I get a #VALUE! error.

Please Login or Register  to view this content.

Any help would be appreciated.

**mcmahobt** · 01-09-2015, 10:20 AM

Instead of using nested IF statements, you could create a lookup table and use VLOOKUP or INDEX/MATCH to return the values. This makes your formula scalable, and negates you having to manually go in and change every word in quotations manually. If you can upload a sample workbook, more detailed help can be given.

**XOR LX** · 01-09-2015, 10:22 AM

Hi.

I think you mean:

=IF(Master!C118="None","",IF(Master!C118=Master!B415,"Pair",IF(COUNTIF(Master!B418:Master!B446,Master!C118),"Each",IF(COUNTIF(Master!B450:Master!B454,Master!C118),"Metre",""))))

Regards

**6StringJazzer** · 01-09-2015, 10:24 AM

It doesn't mean anything for a single cell to be equal to a range of cells. What do you mean there? Do you mean if C118 is equal to any cell in the range B418:B446? If so, one way to do it would be:

Formula:

Please Login or Register  to view this content.

**Pete_UK** · 01-09-2015, 10:25 AM

It would be easier to use a VLOOPUP formula in conjunction with a small table. I don't know what is contained in cells B415, B418:B446, or B450:B454 on the Master sheet as you didn't attach a workbook, but if you copy those cells to (for example) X1, X2 etc. and put the corresponding "Pair", "Each", "Meter" in column Y against each cell, then you could have this formula:

=IF(Master!C118="None","",IFERROR(VLOOKUP(Master!C118,Master!$X:$Y,2,0),""))

Hope this helps.

Pete

**Richie Bell** · 01-09-2015, 10:56 AM

SOLVED

Thank you all for the quick answers.

I plugged in both suggestions from 6StringJazzer and XOR LX, and they both worked.

I hadn't thought about a VLOOKUP table, I will try that as well when I have more time, this is a work in progress!
I have updated everyones rating.

IF function to return text if a cell = another range of cells

LinkBack

Thread Tools

Rate This Thread

Display

IF function to return text if a cell = another range of cells

Re: IF function to return text if a cell = another range of cells

Re: IF function to return text if a cell = another range of cells

Re: IF function to return text if a cell = another range of cells

Re: IF function to return text if a cell = another range of cells

Re: IF function to return text if a cell = another range of cells

Thread Information

Users Browsing this Thread

Similar Threads

Find a text and return the sum of values in adjacent cells matching the text in range

search a range of cells for text and return that text

[SOLVED] IF range of cells contains string of text, return contents of cell where string is found

[SOLVED] Return specific text if a different text is contained anywhere in a range of cells

HELP! using the IF function to verify a text value in a range of cells an return a value.

Bookmarks

Bookmarks

Posting Permissions