Posted this internally, but decided this would be a better place to put it.
Hey guys, I am trying to do a simple HLOOKUP in cell B1 from an array that changes based on a value placed into cell A1 (See file for reference).
Two problems I've run up against:
1. I can't seem to be able for it to take =IF(A1="1",TRUE,FALSE). Instead I have to point it to another field like this =IF(A1=A9,TRUE,FALSE). Not a big deal, but curious.
2. When returning the HLOOKUP value from the 2nd row in the array (A3:C6) based on the LOOKUP value as TRUE, I get a screwed up answer from the array. With "1" in A1 I get "G" instead of "A". If I place "2" or "3" in A1, I get the correct HLOOKUP value ("D" and "G", respectively).
Not shure it will work in Excell, but for 1 you might try:
cause 0 is usually affiliated with false, where all other values are true. It could also have something to do with the fact that you have a second "=" sign in the equation, which might confuse Excell. Maybe you'll need brackets "()" for it or something?
As for the second one, I have no idea. Haven't touched Excell in over a year, and never done such scripts...
Figured it out.
1. Had quotes around the number, only need quotes around text.
2. Had redundant negatives, i.e. multiple FALSE responses. I changed the responses to TRUE, or FALSE1/2/3 and it worked.
PS Excel is Evil.