Vlookup for different columns in worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Haz - 18 May 2008 22:02 GMT

Hi,

I have numbers in col A , which i would like to match in col E and if there
is a match i want it to return that exact number in col M. Would it then be
possible to return the values of that row Col F,G,H,I,J,K,L by copy across
this function?

Any help would be apprecaited.

Thanks
Haz

Reply to this Message

Bernard Liengme - 18 May 2008 23:23 GMT

I am not clear on the question. But have a look at INDEX and MATCH and come
back if more questions
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Hi,
>
[quoted text clipped - 9 lines]
> Thanks
> Haz

Reply to this Message

Max - 19 May 2008 00:38 GMT

> I have numbers in col A, which i would like to match in col E
> and if there is a match i want it to return that exact number in col M.

In M1: =IF(ISNUMBER(MATCH(A1,E:E,0)),A1,"")
Copy M1 down to the last row of data in col A

> Would it then be possible to return the values of that row
> Col F,G,H,I,J,K,L by copy across this function?

Sure. This will deliver it further based on col M
In N1: =IF($M1="","",INDEX(F:F,MATCH($A1,$E:$E,0)))
Copy N1 across to T1, fill down
This returns required results from cols F to L

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Haz - 19 May 2008 13:07 GMT

Hi Max,
Thanks for getting back to me This works perfectly, However I forget to
mention that data in col A stops at A92 but the matching Col's E - F continue
down to row 209. As a result this fuction is not picking up everyone. Is it
possible to adjust this function or will i need a Vlookup now?

Many Thanks

Haz

> > I have numbers in col A, which i would like to match in col E
> > and if there is a match i want it to return that exact number in col M.
[quoted text clipped - 9 lines]
> Copy N1 across to T1, fill down
> This returns required results from cols F to L

Reply to this Message

Max - 19 May 2008 14:12 GMT

I'm not sure if its possible

Can you upload your sample file/data* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi Max,
> Thanks for getting back to me This works perfectly, However I forget to
[quoted text clipped - 7 lines]
>
> Haz

Reply to this Message

Haz - 21 May 2008 08:21 GMT

this works perfectly, I have added a handful of entries by cut & paste so
need to adjust function here.

> I'm not sure if its possible
>
[quoted text clipped - 18 lines]
> >
> > Haz

Reply to this Message

Max - 21 May 2008 22:48 GMT

glad you sorted it out over there

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> this works perfectly, I have added a handful of entries by cut & paste so
> need to adjust function here.

Reply to this Message