Is it possible to do a formula within a VLOOKUP when the return answer could
be more than one thing.
I.e., if I have a list of property numbers but there are several lease
numbers for each one. I only want to pull back property 123 is it has lease 4
as well. It may have lease 1, 2, 3. Normally with a vlookup it brings back
only the first answer whereas I need to lookup the answer too.
If that makes sense...

Not sure what you mean, but do look he

http://office.microsoft.com/en-us/as...260381033.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jen" wrote in message ...
|
| Is it possible to do a formula within a VLOOKUP when the return answer could
| be more than one thing.
| I.e., if I have a list of property numbers but there are several lease
| numbers for each one. I only want to pull back property 123 is it has lease 4
| as well. It may have lease 1, 2, 3. Normally with a vlookup it brings back
| only the first answer whereas I need to lookup the answer too.
| If that makes sense...

I don't believe you can do it with Vlookup but you can do it with Index. The
formula below has the following arguments:-

A2:B500. the lookup array
J1 = what to look for
K1 the instance to look for 1 for first or 2 for 2nd etc
It returns the value from the second column (The last 2 in the formula.
It's an array so Ctrl+shift+enter


=INDEX($A$2:$B500,SMALL(IF($A$2:$B500=$J$1,ROW($A$ 2:$B500)-ROW($A$2)+1,ROW($B500)+1),K1),2)

Mike

"Jen" wrote:


Is it possible to do a formula within a VLOOKUP when the return answer could
be more than one thing.
I.e., if I have a list of property numbers but there are several lease
numbers for each one. I only want to pull back property 123 is it has lease 4
as well. It may have lease 1, 2, 3. Normally with a vlookup it brings back
only the first answer whereas I need to lookup the answer too.
If that makes sense...

I've tried this and it's coming up #NUM! I can see the logic in the formula
but can't see where I'm going wrong. Here is my formula-
=INDEX(A1:B7,SMALL(IF(A1:B7=B1,ROW(A1:B7)-ROW(A1)+1,ROW(B7)+1),K1),2)

And ideas?
My array is:
Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834


You cannot imagine how much easier this will make life for people I know and
myself if you can help me!

"Mike H" wrote:

I don't believe you can do it with Vlookup but you can do it with Index. The
formula below has the following arguments:-

A2:B500. the lookup array
J1 = what to look for
K1 the instance to look for 1 for first or 2 for 2nd etc
It returns the value from the second column (The last 2 in the formula.
It's an array so Ctrl+shift+enter


=INDEX($A$2:$B500,SMALL(IF($A$2:$B500=$J$1,ROW($A$ 2:$B500)-ROW($A$2)+1,ROW($B500)+1),K1),2)

Mike

"Jen" wrote:


Is it possible to do a formula within a VLOOKUP when the return answer could
be more than one thing.
I.e., if I have a list of property numbers but there are several lease
numbers for each one. I only want to pull back property 123 is it has lease 4
as well. It may have lease 1, 2, 3. Normally with a vlookup it brings back
only the first answer whereas I need to lookup the answer too.
If that makes sense...

Jen,

It's the B1 in the formula that's the problem, it's inside the array. Change
it to C1.

=INDEX(A1:B7,SMALL(IF(A1:B7=C1,ROW(A1:B7)-ROW(A1)+1,ROW(B7)+1),K1),2)

This works fine.

Mike

"Jen" wrote:

I've tried this and it's coming up #NUM! I can see the logic in the formula
but can't see where I'm going wrong. Here is my formula-
=INDEX(A1:B7,SMALL(IF(A1:B7=B1,ROW(A1:B7)-ROW(A1)+1,ROW(B7)+1),K1),2)

And ideas?
My array is:
Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834


You cannot imagine how much easier this will make life for people I know and
myself if you can help me!

"Mike H" wrote:

I don't believe you can do it with Vlookup but you can do it with Index. The
formula below has the following arguments:-

A2:B500. the lookup array
J1 = what to look for
K1 the instance to look for 1 for first or 2 for 2nd etc
It returns the value from the second column (The last 2 in the formula.
It's an array so Ctrl+shift+enter


=INDEX($A$2:$B500,SMALL(IF($A$2:$B500=$J$1,ROW($A$ 2:$B500)-ROW($A$2)+1,ROW($B500)+1),K1),2)

Mike

"Jen" wrote:


Is it possible to do a formula within a VLOOKUP when the return answer could
be more than one thing.
I.e., if I have a list of property numbers but there are several lease
numbers for each one. I only want to pull back property 123 is it has lease 4
as well. It may have lease 1, 2, 3. Normally with a vlookup it brings back
only the first answer whereas I need to lookup the answer too.
If that makes sense...