Lookup, but which type?

[Akashwani]

Hi,

Before I have to call in the builders to replace the wall I'm banging my head on, can someone point me in the right direction to solving this?

I don't want the actual formula (yet), just a pointer.
I'm probably missing the bl**dy obvious here.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	Dog	Cat	Pig	Horse	Fish	Panda	Rat	Mouse	Moose	Cow	Bull
3	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	Fish	Panda	*	*
Sheet3

If I type Fish in H4, which type of formula do I require in I4 to return Panda?
Like wise If I type Dog in H4 I want to return Cat in I4.

I look forward to the sniggers and help

Thanks

Ak

 

[RSXchin]

nevermind, I'm wrong

 

[wigi]

For me, INDEX with a MATCH nested into it.

 

[Aladin Akyurek]

Hi,

Before I have to call in the builders to replace the wall I'm banging my head on, can someone point me in the right direction to solving this?

I don't want the actual formula (yet), just a pointer.
I'm probably missing the bl**dy obvious here.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	Dog	Cat	Pig	Horse	Fish	Panda	Rat	Mouse	Moose	Cow	Bull
3	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	Fish	Panda	*	*
Sheet3

If I type Fish in H4, which type of formula do I require in I4 to return Panda?
Like wise If I type Dog in H4 I want to return Cat in I4.

I look forward to the sniggers and help

Thanks

Ak

Assuming that a look up value occurs once at most in A2:K2...

=IF(LOOKUP(REPT("z",255),A2:K2)=H4,"",INDEX(A2:K2,MATCH(H4,A2:K2,0)+1))

 

[T. Valko]

Hi,

Before I have to call in the builders to replace the wall I'm banging my head on, can someone point me in the right direction to solving this?

I don't want the actual formula (yet), just a pointer.
I'm probably missing the bl**dy obvious here.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	Dog	Cat	Pig	Horse	Fish	Panda	Rat	Mouse	Moose	Cow	Bull
3	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	Fish	Panda	*	*
Sheet3

If I type Fish in H4, which type of formula do I require in I4 to return Panda?
Like wise If I type Dog in H4 I want to return Cat in I4.

I look forward to the sniggers and help

Thanks

Ak

TRy this...

=INDEX(A2:K2,MATCH(H4,A2:K2,0)+1)

 

[Akashwani]

Hey hey,

I've used this without checking the answers given.....

=INDEX(A2:K2,MATCH(H4,A2:K2,0)+1)

I kept missing off the +1

Ak

Thanks for the replies and answers people, I managed to sort it out myself, thanks.

Aladin what does the first part of your formula have to do with the result?
Is there a joke in there?

Thanks all.

Ak

 

[Aladin Akyurek]

Hey hey,

I've used this without checking the answers given.....

=INDEX(A2:K2,MATCH(H4,A2:K2,0)+1)

I kept missing off the +1

Ak

Thanks for the replies and answers people, I managed to sort it out myself, thanks.

Aladin what does the first part of your formula have to do with the result?
Is there a joke in there?

Thanks all.

Ak

Yes, it's a joke. Just enter Bull in H4...

 

[wigi]

Aladin's suggestion was to return a "blank" cell if H4 happened to be found in K2.

 

[wigi]

Yes, it's a joke. Just enter Bull in H4...

And if L2 is empty you have the same result...

 

[Akashwani]

Ah,
and here's me thinking that I'm going to get 255 z's,

Thanks for your time people.

Now for some DIY!!
Anyone have any pollyfilla?

Ak