Hlookup negative number?

hi, I have a question using an Hlookup function. I was wondering if it was possible to find a number that is located in a cell above the "value" in the Hlookup formula?

for example:

1 2 3 4 5 6 7
yes no maybe please haha monster 12 pk
345 456768 76769 1234 12345 3456 345
whhhha 234j 23jhk m34 9834 ok 234

to find the header number associated with "monster" would I use
=hlookup("monster",A1:G4,-2,False)

I know that if I enter a 2 as the index_number it will show me the second value in that column BELOW monster, but I want to find the value ABOVE monster. is there a way to look up values above the it?

=index(A1:G1,match("monster",A2:G2,0))

awesome that helps so much! thanks.


however, part 2 to this formula presents another problem to me. I dont want to manually enter the word for each value, rather I would prefer to have it refrence the list of values I need to look up. this is what it looks like in practice:

in one column i have a list of values I need to look up:
green
red
blue
orange
purple
white
black
yellow

then in another colum I have the formulas to look them up:
=INDEX(CBU!A2:EH2,MATCH("green",CBU!A6:EH6,0))
=INDEX(CBU!A2:EH2,MATCH("red",CBU!A6:EH6,0))
=INDEX(CBU!A2:EH2,MATCH("blue",CBU!A6:EH6,0))
=INDEX(CBU!A2:EH2,MATCH("orange",CBU!A6:EH6,0))
=INDEX(CBU!A2:EH2,MATCH("purple",CBU!A6:EH6,0))
….
…..
………
…………


is there a way to auto fill in these formulas with the next value in the list? the problem I get is that, because the index formula is looking up an "alphabetic word", simply entering "A1,A2,A3..." doesnt work. If I manually type in "red, blue, green, purple etc" it looks it up just fine. how can I automate this process?


thanks

can you attach a sample???