Developer Forum

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.

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.



--

Dave Peterson