vlookup/hlookup, or what?

Hello all, I have a lookup problem.

I try to lookup a certain value which can be found in column A:T (in one certain row), and then display field Z of that certain row.

So lookup Marc and display 5.

john

mia

seth

marc

eva

5

Any ideas?

Thanks!
Martijn

Perhaps

=IF(COUNTIF(A1:T1,"Marc"),Z1,"")

gone try that, thanks. But does it work if I have 100 rows and the "marc" will be in one of those 100?

No, I took your statement "in one certain row" to mean that you already knew which row marc was in.

So you're saying then that Marc could be anywhere in A:T ?
Will Marc only appear ONCE in the entire range?
If it could be multiple times, which one do you want to use?

I dont know in which row Marc will appear, and yes it will only be once.

Originally Posted by Tony Valko

Assuming that Marc appears only once...

Array entered**:

=INDEX(X2:X100,MAX(IF(A2:T100="Marc",ROW(A2:T100)))-ROW(A2)+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks, but somehow I keep getting #value!

I can see 3 possible causes for that formula to return #Value!

1. You didn't enter it with CTRL + SHIFT + ENTER
It's an array formula that requires special keystrokes to enter it.
Instead of just pressing enter after typing in the formula, you press CTRL + SHIFT + ENTER
You will know when it's correclty entered when you see the formula surronded by {brackets}

2. There are no cells in the range 'exactly' equal to "Marc"
Check the cells for correct spelling and Extra Spaces like " Marc" or "Marc "

3. There are other pre-existing formulas in the range that are resulting in #Value! errors.

Yeah there was a formula somewhere, now it seems to work. Thanks!