-1

I am trying to write a formula that will return the value of one cell if a range of cells equal a value specific in a list.

For example:

    A1 = Orange
    B1:B5 = My Data 
    If B1:B5 = Red, Blue, or Green, display the value from A1

Thanks for your help!

Community
  • 1
  • 1
B.Witt
  • 1
  • 1
  • 1

2 Answers2

0
Dim a as Range, listValues() as string 'array of string
Dim str as string, i as integer


str = "Red;Blue;Green"
listValues = Split(str,";") 'split str using ";"
for i= Lbound(listValues) to Ubound(listValues) 'loop thru the array
    Set a = ActiveSheet.UsedRange.Find(what:=listValues(i), lookat:=xlWhole) 
    if Not a Is Nothing Then
        'value found
        a.value = Range("A1").value
    end if
next i

I've adapted @Davesexcel 's resolution, check his one: How to avoid using select in VBA for variable cell ranges?

This resolution from RonDeBruin is nice too:

http://www.rondebruin.nl/win/s9/win006.htm

Community
  • 1
  • 1
Alef Spinella
  • 43
  • 5
0

Put your values some where in a column:

enter image description here

Then a simple formula in C1:

=IF(ISNUMBER(MATCH(B1,E:E,0)),$A$1,"")

If a match is found it will return its row number. The If tests if it is a number or an error. If it is a number, a match is found and the value in A1 is returned. Otherwise it returns an empty string.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you so much! – B.Witt Aug 09 '16 at 15:29
  • I got it to work on a single cell however I need to have it verify on a range. When I use the below formula it doesn't display the value from A9 even though the logic test returns true. Any suggestions? =IF(ISNUMBER(MATCH(F9:U9,W43:W50,0)),$A$9,"") – B.Witt Aug 09 '16 at 15:29
  • You will need to enter that formula with Ctrl-Shift-Enter instead of Enter when leaving Edit mode. If done properly then Excel will put `{}` around the formula. @B.Witt – Scott Craner Aug 09 '16 at 15:35