Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trying to eliminate some excel vlookups

Status
Not open for further replies.

sahmiele

Technical User
Sep 9, 2003
67
US
I have an excel workbook, that uses a ton of vlookups. In each row, there are 7 vlookups. They pull product information (description, price, size, etc) from a worksheet (PRICE BOOK) based on a sku number (Column A) on the main worksheet (TEMPLATE). Is there a way I can find the row number of sku on the price book worksheet, and then use that value as a reference for the other cells? Thanks in advance.
 


Hi

Check out the MATCH & INDEX functions.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
I've looked at them, but can't seem to get them to work. What are the values I'm supposed to be getting from them?
 



Your LOOKUP value is the lookup in the MATCH,

The value from the MATCH is used as the ROW offset in the INDEX. The COLUMN value you are returning, is the RANGE in the INDEX.

So...

if your VLOOKUP looks like this...
[tt]
=vlookup(LookupValue,LookupRange[/i],[ColumnOffset[/i])
[/tt]
then your MATCH would be, (let's say in column C)...
[tt]
C2: =MATCH(LookupValue,FirstColumn of LookupRange,0)
[/tt]
and your INDEX would be (let's say in column D)...
[tt]
D2: =INDEX(ColumnOffset of LookupRange[/u],C2,1)
[/tt]
chere C2 is your MATCH return row offset.



Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top