software

vlookup e.g.

Line 1 (Table)(Col A)REF123(ColB)245
Line 2 (Table)(Col A)REF123(ColB)246
Line 3 (Table)(Col A)REF123(ColB)247 and so on..

On column C(above) i need to return column b from the array.(below)

Line 1 (Array)(Col A)REF122(ColB)243
Line 2 (Array)(Col A)REF123(ColB)244
Line 3 (Array)(Col A)REF123(ColB)245

when i do a vlookup i have to use column A for the data but when i ask for
column b to be returned it dosent match (Line 1 on Table pulls through line 2
on array when i need line 3 which is the correct match.

Is it possible to do this?
How would it know, it returns the first match.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;D7ONOquot; gt; wrote in message
...
gt; vlookup e.g.
gt;
gt; Line 1 (Table)(Col A)REF123(ColB)245
gt; Line 2 (Table)(Col A)REF123(ColB)246
gt; Line 3 (Table)(Col A)REF123(ColB)247 and so on..
gt;
gt; On column C(above) i need to return column b from the array.(below)
gt;
gt; Line 1 (Array)(Col A)REF122(ColB)243
gt; Line 2 (Array)(Col A)REF123(ColB)244
gt; Line 3 (Array)(Col A)REF123(ColB)245
gt;
gt; when i do a vlookup i have to use column A for the data but when i ask for
gt; column b to be returned it dosent match (Line 1 on Table pulls through
line 2
gt; on array when i need line 3 which is the correct match.
gt;
gt; Is it possible to do this?
gt;
gt;

Hi,
As Bob has said, vlookup isn't that smart - amp; I may be slightly off
track here, but why is line 3 the correct match?
If it's b/c this row (ie column A value amp; column B value) is identical
in both the table amp; the array, what is the point of returning column B
(of array) in column C of the Table (you could just typequot;=B1quot;)?

If your intention is to return a value from an identical row you need
to use a concatenated helper column (eg, cell C1 contains quot;=A1amp;quot;%quot;amp;B1quot;)
for your lookup/matching or a sum product formula.

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=539199with the data i have i firstly have to do the lookup by using column a to
match the data however columns b also have to match as they can be different
which is why line 3 is correct (colB is 245 on both sheets) whereas line 2 on
the array column b is 244 this is why i need a formula to search on column b
after the initial lookup.

hope this makes sense

quot;broro183quot; wrote:

gt;
gt; Hi,
gt; As Bob has said, vlookup isn't that smart - amp; I may be slightly off
gt; track here, but why is line 3 the correct match?
gt; If it's b/c this row (ie column A value amp; column B value) is identical
gt; in both the table amp; the array, what is the point of returning column B
gt; (of array) in column C of the Table (you could just typequot;=B1quot;)?
gt;
gt; If your intention is to return a value from an identical row you need
gt; to use a concatenated helper column (eg, cell C1 contains quot;=A1amp;quot;%quot;amp;B1quot;)
gt; for your lookup/matching or a sum product formula.
gt;
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=539199
gt;
gt;

Seems odd that you want to find a value that you already have?

=INDEX(Table!B1:B100,MATCH(1,(Table!A1:A100=Array! A1)*(Table(B1:B100=Array:B
1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;D7ONOquot; gt; wrote in message
news
gt; with the data i have i firstly have to do the lookup by using column a to
gt; match the data however columns b also have to match as they can be
different
gt; which is why line 3 is correct (colB is 245 on both sheets) whereas line 2
on
gt; the array column b is 244 this is why i need a formula to search on column
b
gt; after the initial lookup.
gt;
gt; hope this makes sense
gt;
gt; quot;broro183quot; wrote:
gt;
gt; gt;
gt; gt; Hi,
gt; gt; As Bob has said, vlookup isn't that smart - amp; I may be slightly off
gt; gt; track here, but why is line 3 the correct match?
gt; gt; If it's b/c this row (ie column A value amp; column B value) is identical
gt; gt; in both the table amp; the array, what is the point of returning column B
gt; gt; (of array) in column C of the Table (you could just typequot;=B1quot;)?
gt; gt;
gt; gt; If your intention is to return a value from an identical row you need
gt; gt; to use a concatenated helper column (eg, cell C1 contains quot;=A1amp;quot;%quot;amp;B1quot;)
gt; gt; for your lookup/matching or a sum product formula.
gt; gt;
gt; gt; Rob Brockett
gt; gt; NZ
gt; gt; Always learning amp; the best way to learn is to experience...
gt; gt;
gt; gt;
gt; gt; --
gt; gt; broro183
gt; gt; ------------------------------------------------------------------------
gt; gt; broro183's Profile:
www.excelforum.com/member.php...oamp;userid=30068
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=539199
gt; gt;
gt; gt;