UPDATED - Referencing named Ranges within a Nested IF formula

J

JTinAtlanta

note - I referred to my ranges as lists. Wasn't sure of the impact, if any,
that would have to my data. I went ahead and updated this request. I REALLY
appreciate any help on this, as I have limited documentation regarding Excel
syntax in formulas. Thanks again.

All,

I am trying to create a formula that will evaluate a cell against 3 named
ranges,
populating another cell with the value assigned to the matching range or
feeding it with a 0 if it doesn't match values in any of the ranges. Is this
possible? Thank you in advance!!!

Cell_1 : 'Manufacturing'
Cell_2 : <cell where returned value needs to be placed

RANGE_1 RANGE_2 RANGE_3
'Shipping' 'Medical' 'Construction'
'Engineering' 'Banking' 'Entertainment'
'Manufacturing

Cell_3 = 1 (RANGE_1 score)
Cell_4 = 5 (RANGE_2 score)
Cell_5 = 9 (RANGE_3 score)

Logic:
IF Cell_1 in LIST_1, then Cell_2 = Cell_3 ELSE
IF Cell_2 in LIST_2, then Cell_2 = Cell_4 ELSE
IF Cell_3 in LIST_3, then Cell_2 = Cell_5 ELSE
Cell_2 = 0
END-IF
END-IF
END-IF

Regards,

JT
 
B

Biff

Hi!

Try this:

A1 = manufacturing
B1 = formula:

=IF(ISNUMBER(MATCH(A1,List_1,0)),1,IF(ISNUMBER(MATCH(A1,List_2,0)),5,IF(ISNUMBER(MATCH(A1,List_3,0)),9,"")))

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing lists in a nested IF formula 1
A find formul looking at two named ranges for two strings 8
Named range with multiple groups of cells 4
Macro to loop through a named range, ignoring empty cells 1
Phantom named ranges in a workbook? 3
Referencing Named Ranges using values in a cell 2
If Value is > 0, in a named range ?? 2
macro to make named ranges 7

Top