VLOOKUP but return a different row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cultgag - 28 Feb 2006 17:07 GMT

Hi,

I am trying to match a part number with a separate .csv file to update
inventory. My dilemma is I am using VLookup to accomplish this but
there has been some changes to the .csv file format and now the
inventory on hand is on a separate row with the part number. Now if my
understanding is correct, since the vlookup function can only return
values from the same row it will not work with what I need...

Any help is greatly appreciated!

TIA~

Signature

cultgag

Reply to this Message

dmexcel - 28 Feb 2006 17:20 GMT

one way would be to seperate the cell using
Data=>text to columns
select comma , if the files join together all the time when they are
updated, you could always record a macro that would do this routine
then you would assign it to a button!

Reply to this Message

Chris Marlow - 28 Feb 2006 17:26 GMT

Hi,

I'd Use MATCH (similar parameters to vlookup) to return the row & either
INDIRECT or INDEX to return the values of the cell(s) in the row below by
incrementing the result of the MATCH by 1.

Regards,

Chris.

Signature

Chris Marlow
MCSD.NET, Microsoft Office XP Master

> Hi,
>
[quoted text clipped - 8 lines]
>
> TIA~

Reply to this Message

Jim Thomlinson - 28 Feb 2006 17:49 GMT

I would be more inclined to go with index over indirect for purely
performance reasons. Indirect is volatile and will slow thing down... Here is
a good reference...

http://www.decisionmodels.com/optspeede.htm

Signature

HTH...

Jim Thomlinson

> Hi,
>
[quoted text clipped - 18 lines]
> >
> > TIA~

Reply to this Message

cultgag - 28 Feb 2006 19:18 GMT

the index with the match function works great!

Thanks for the replies! :)

Signature

cultgag

Reply to this Message