Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2007

Tip: Looking for answers? Try searching our database.

match row, then match column, then get header for that column?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jg - 20 Jun 2007 18:21 GMT
I have a pricelist file with SKUs in column A and prices for different types
of accounts in columns B-E.  In another file, I have sales by SKU, with
price paid.  Now I want to add the type of account to the sales file.

So, I need to find the right row in the pricelist file by matching to the
SKU in the sales file, then in that row find the price paid, then get the
label of the column in which that price appears.

For example:
- Lets say I have an entry in the sales file with SKU  of ABC123, for which
the customer paid $65.
- Now I want to go down the list of SKUs in the pricelist until I find the
row for ABC123.  Say it's in row 50.
- Next, I want to go over in row 50 until I find the column with $65 in it.
Lets say its in column D.
- I want to return the label that shows up in D1 of the pricelist ("Dealer",
for instance) to my new cell in the Sales file.

(PS, the sales file has 50k rows, the pricelist has 2500 rows).

I've been fooling around with index, match, indirect and address, but I
can't work out how to do it.  Any help greatly appreciated.
Dave Peterson - 20 Jun 2007 19:51 GMT
This worked for me:

I put my test data in Sheet1 rows A1:H30 (headers in row 1 and column A).

Then in Sheet2:
I put the sku in A1
and the price in B1
And this formula in C1:

=INDEX(Sheet1!$1:$1,
  MATCH(B1,OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!$A:$A,0)-1,,1,256),0))

And if there was an exact match for both the SKU and the price, it returned the
header--else it returned an #n/a error.

> I have a pricelist file with SKUs in column A and prices for different types
> of accounts in columns B-E.  In another file, I have sales by SKU, with
[quoted text clipped - 18 lines]
> I've been fooling around with index, match, indirect and address, but I
> can't work out how to do it.  Any help greatly appreciated.

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.