need help with a vlookup but returning a particular match?

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)

"D7ONO" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

Hi,
As Bob has said, vlookup isn't that smart - & 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 & column B value) is identical in both the table & the array, what is the point of returning column B (of array) in column C of the Table (you could just type"=B1")?

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

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

with 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

"broro183" wrote:

>
> Hi,
> As Bob has said, vlookup isn't that smart - & 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 & column B value) is identical
> in both the table & the array, what is the point of returning column B
> (of array) in column C of the Table (you could just type"=B1")?
>
> If your intention is to return a value from an identical row you need
> to use a concatenated helper column (eg, cell C1 contains "=A1&"%"&B1")
> for your lookup/matching or a sum product formula.
>
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=539199
>
>

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)

"D7ONO" <[email protected]> wrote in message
news:[email protected]...
> with 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
>
> "broro183" wrote:
>
> >
> > Hi,
> > As Bob has said, vlookup isn't that smart - & 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 & column B value) is identical
> > in both the table & the array, what is the point of returning column B
> > (of array) in column C of the Table (you could just type"=B1")?
> >
> > If your intention is to return a value from an identical row you need
> > to use a concatenated helper column (eg, cell C1 contains "=A1&"%"&B1")
> > for your lookup/matching or a sum product formula.
> >
> > Rob Brockett
> > NZ
> > Always learning & the best way to learn is to experience...
> >
> >
> > --
> > broro183
> > ------------------------------------------------------------------------
> > broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
> > View this thread:
http://www.excelforum.com/showthread...hreadid=539199
> >
> >