Novice still needs help
Posted by Al on December 28, 2001 6:53 PM
If BZ4=1 I need to lookup a name in BZ5 then find that name in AM4:BQ4, and when that name is found, Example, (if the name was found in BC4 then I need to take the number in BC39 and put it in CA7.)
I was given two examples a few days ago but they did not work, I may not have made it very clear what I was looking for. perhaps this explanation is a little clearer
Posted by Scott on December 28, 2001 7:05 PM
I'm not sure what the relationship is to the cells, but you could use a lookup formula to look up the name if you could format all the names to be in one column, or one row, with the corrosponding number (the value you want to return) either in the column to the right, or the row below the names. If it's possible to format your work this way, I can help you with the formula.
Posted by Al on December 28, 2001 7:49 PM
The only name that is not in the same column is in BZ5, all the rest are in the same row, AM4:BQ4 Thanks for your help. AL
Posted by Scott on December 28, 2001 9:08 PM
Ok, then try this, in Cell CA7 type the following formula:
=IF(BZ4=1,HLOOKUP(BZ5,AM4:BQ39,36,0),"")
If your data is larger than row 39, then change BQ39 to BQ(last row#).
Posted by AL on December 28, 2001 9:26 PM
Scott All I get is #N/A
Posted by Scott on December 28, 2001 9:37 PM
Just to confirm: This formula should be in cell CA7. Cell BZ4 should equal 1. BZ5 should contain a name that you want to look up. This name should also be listed in row 4 between columns AM and BQ. The name must be spelled exactly the same here as in BZ5. Once it finds the name in this row, it will look down 36 rows counting row 4 and return this value. In your example, you wanted the value returned from row 39 which would be 36 down from the name (including the name). This is how I understood your data when I wrote the formula. If this is correct, then the formula should work. If I got something wrong, let me know, and I will adjust the formula.
Posted by al on December 28, 2001 9:53 PM
Scott Your formula works great, I had the name spelled two different ways and I did not realize it until you pointed out about the names being spelled alike. I really appreciate your help, thank you very much. AL
