Dynamically defined names

[spacely]

Hello,

I was looking into "dynamically defined names", but am not sure that's what will work for me...

I have values changing in column A, and based on that value, the cells in column B (same row) should have a particular defined name. But there could be several possible dynamically defined names assigned. So, i.e. if A4 = 4% and A11 = 19%, the assignment would find them both, and assign B4 as MyFirstOne, and B11 as MySecondOne.

Would like to avoid VBA, but it's OK if we need to go there

Thanks a lot!

Dave

 

[spacely]

Yes, in refers to I have

=INDEX(Sheet1!$B:$B, MATCH("4", Sheet1!$A:$A,0), 1)

...and Sheet1 has the column A and B I showed above. I added the quotes around 4 after it also didn't work w/o quotes, so either way not capturing the 4. I wonder if it should be an array formula...

 

[spacely]

Oh interesting! I instead entered column A entries like ="1", ="2", ... ="4"... and now it works.

Is that match only on strings? Are my cells really strings??

 

[mikerickson]

The formula that I posted does not have quotes around the 4.

 

[spacely]

yes I know. And when I take them off, it now works, but only after I retype the numbers as normal numbers and not as ="4" for example. Not sure why it works now.

 

[mikerickson]

Without the quotes, the formula is looking for the number 4. With the quotes it is looking for the string "4". They are different.