If or Match Problem

**j.farr3ll** · 08-31-2010, 05:32 AM

I have a table of 42 products which have been assigned 5 different valuations each, however the products aren't actualy valued in order so i need something that looks at the table of valuations matchs what the product valuation is and then returns the same valuation to where the product has been valued in a random order.

Might make more sense to look at the spreadsheet...

Thanks in advance,

James

**estige** · 08-31-2010, 06:37 AM

If I have understood you correctly, try:
=VLOOKUP($A14,$J$2:$O$46,COLUMN(),FALSE)
Copy across and down

**j.farr3ll** · 08-31-2010, 06:52 AM

Yeh that works perfectly on the cut down dataset i included here, but returns #REF when i try it in my main dataset, even when the paramameters are moved to cover the same data. Any ideas why it might do this?

**j.farr3ll** · 08-31-2010, 06:58 AM

Don't worry was just being an idiot, have sorted it, thanks for your help

James

**estige** · 08-31-2010, 07:05 AM

Maybe it has to do with the use of column to decide what column in the matrix the result shall be picked from?

In my solution column B (=column 2) gets data from column K, which is column 2 in the lookup-matrix.

If this is the problem, just add or subtract from COLUMN() to adjust, or use the actual number instead.