+ Reply to Thread
Results 1 to 2 of 2

IF formula exceeds NESTING limit

  1. #1
    Registered User
    Join Date
    12-26-2019
    Location
    charlotte, nc
    MS-Off Ver
    the latest
    Posts
    2

    IF formula exceeds NESTING limit

    I'm trying to create a "tool" that when I cut and paste a list of numbers, similar to the ones below into a column with the IF formula, the TRUE result will yield the text beside it's corresponding number. I exceeded the NESTING limit of 64 by using, IF(A1=271817,"MIDNIGHT",IF(A1=100151,"TRITON GREY",IF(A1=100153,"TRITON BROWN, so and so forth until I had entered the entire list. Is there another way of achieving the desired results? Mind you, I'm a novice at this and may not pick up on tech short hand explanations.

    Thanks,

    Dave

    271817 MIDNIGHT
    100151 TRITON GREY
    100153 TRITON BROWN
    100155 STAR SILVER
    100157 STAR COPPER
    100159 SATURN BLUE
    100161 SATURN WHITE
    147837 ATHENA ROSE
    147883 DESERT SAND
    147983 REVERE SILVER
    147990 ANTIQUE SILVER
    147992 GOLDTONE
    147994 MANDARIN
    147995 EMERALDTONE
    147998 REVERE SILVER
    148001 FRANKLIN SILVER
    148013 MERCURY
    148015 ORION
    148018 CHURCHILL BLUE
    148026 GRAYTONE
    148028 EARTHTONE
    148029 REVERE SILVER
    148030 CHURCHILL BLUE
    148031 ROMAN
    148035 MISTY ROSE ROSE
    148038 SILVER CARNATION
    148040 IVORY GOLD
    148041 ANTIQUE BLUE
    148043 PRIMROSE ROSE
    148045 EBONYTONE
    148046 SILVER TUSCANY
    148049 WINEBERRY
    148050 SIRRUS
    148052 GALAXY
    148054 LUMINA
    148056 POLARIS
    148060 CORETTA
    148062 REVERE SILVER
    148063 CHURCHILL BLUE GOING HOME
    148068 HEIRLOOM PEWTER
    148070 ROMAN
    148073 SUNGLOW
    148075 VINTAGE
    148077 DESERT CHAMPAGNE
    148078 ROSE BRONZE
    148080 CORTEZ GOLD BIBLE
    148081 HEIRLOOM PEWTER BIBLE
    148083 MISTY BLUE
    148086 CARNATION MIST ROSE
    148088 IVORY MIST GOING HOME
    148089 EARTHTONE
    148090 SILVER EBONY-FC
    148091 SILVER EBONY
    212677 AUBURN
    217298 GEMINI SILVER
    217379 GEMINI WHITE/PINK
    217383 GEMINI WHITE/WHITE/GOLD
    217387 GEMINI COPPER
    217389 GEMINI BLUE
    221502 APOLLO SILVER
    221503 APOLLO BLUE
    221504 APOLLO COPPER
    221505 APOLLO WHITE/PINK
    221506 SPECTRA BLUE
    221507 SPECTRA SILVER
    223045 SPECTRA WHITE
    234314 ARIES BLUE
    234316 ARIES ORCHID
    234318 ARIES SILVER
    234320 ARIES COPPER
    234322 ARIES WHITE
    241683 APOLLO WHITE/WHITE
    242475 HEIRLOOM PEWTER
    242478 SAND LV
    242739 SAND
    244327 APOLLO FLAT BLACK
    246026 OPAL-28
    246306 SAND-32 LV
    252011 CARNATION BLUSH
    252031 IVORY LS/CAP
    252121 OCEAN BLUE
    252123 OCEAN BLUE-FC
    256354 SPECTRA BLACK
    257953 PISCES WHITE
    257956 PISCES SILVER
    257958 PISCES SAND
    257960 SCORPIO BROWN
    257962 SCORPIO GREY
    258362 SPECTRA ORCHID
    271815 AUBURN
    271819 NEOPOLITAN BLUE
    271821 REVERE SILVER LS4
    272930 ORCHID
    273920 CARNATION
    278177 HUNTINGTON GREEN
    278912 ARGO PAINTED ORCHID
    278913 ARGO PAINTED BLACK
    278920 ARGO PAINTED SILVER
    278921 ARGO PAINTED SAND
    279127 VIRGO BLUE
    279131 VIRGO SILVER
    279133 VIRGO WHITE/GOLD
    279134 VIRGO WHITE/PINK
    279135 VIRGO COPPER

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,729

    Re: IF formula exceeds NESTING limit

    a lookup
    vlookup()
    or index/match
    assuming your list is in columns A2 and B500

    then
    =vlookup(A1,$A$2:$A$500,2,false)
    OR
    =Index($B$2:$B$500, match( A1 , $A$2:$A$500, 0 ))

    however a sample spreadsheet as indicated in the yellow banner would help
    Attached Files Attached Files
    Last edited by etaf; 01-10-2021 at 08:19 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array formula exceeds 255 limit
    By S1n1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-10-2016, 04:54 AM
  2. [SOLVED] Formula that exceeds the 8000 characters limit
    By deweyirl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2016, 02:26 PM
  3. When a Value exceeds limit
    By mills49 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 11:16 PM
  4. Paste Something Into Excel that Exceeds Row Limit
    By Shadefalcon in forum Excel General
    Replies: 5
    Last Post: 04-12-2013, 12:55 PM
  5. Notification when a value exceeds a limit
    By -mads in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2012, 07:43 PM
  6. Replies: 7
    Last Post: 02-29-2012, 12:06 PM
  7. [SOLVED] if the cell value exceeds the limit to have a dialog box appear
    By Dschro in forum Excel General
    Replies: 2
    Last Post: 12-15-2005, 06:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1