Complicated value lookup
 

I have a set of data in this format:

a x 12
b x 14
c x 18
a y etc.
b y
c y
a z
b z
c z

And i want it in this table format:
x y z
a # # #
b # # #
c # # #


What is the relevant formulae? I've tried some mashups of offset, index
and match to no avail.

An example spreadsheet is attached if you want to have a go.

Cheers,
TF


+-------------------------------------------------------------------+
|Filename: Complicated value lookup.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4726 |
+-------------------------------------------------------------------+

--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

The easiest way to achieve your result would be as follows:

Insert an additional column between COLUMN and VALUE

In cell C3 enter formula =A3&B3

In cell H4 enter formula =VLOOKUP(G4&H$3,$C:$D,2,FALSE)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Hi,

I think a pivot table might me easier.

Higlight the range of cells (A2 to C11).
Click Data Pivot Table Next Next Layout.

Drag the Row button to the Row section of the Pivot table, Column
button to Column section and Value to the data section (make sure it
says "Sum of Value")
Click OK Finish and you should get the results you need.

Thanks
gazzr


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Thats good Quattro, I'll make a note of that Vlookup formula.

Gazzr


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Thanks Heaps Captain Quattro and Gazzr!
Both methods work (and are very easy) compared to what i was trying to
do.
Virtual Beers all round!
Cheers,
TF


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Arghhh!

Just when i thought it was all hunky dory!

Pivot table method:
In the data i am now applying it too, the pivot table is too large to
fit on one worksheet (whichever way i do it).
I could probably do two pivot tables after splitting the table, but it
doesn't seem very neat.
It also alphabetised the column data as well, which is not desirable,
and my guess at a workaround is not very neat either.

&,Vlookup method:
There is not a data point for every possible combination of the data.
If there is no data, the default should be zero. Since the formula
returns N/A, i can't make an easy formula out of this. I am planning to
do a two step process - a first table with N/A's in it, and a second
table that uses info from the first table to put zeros instead of
N/A's. Neater solutions anyone?

Cheers,
TF


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

You could probably nest the Captain's lookup formula in an if statement:

=if(isna(VLOOKUP(G4&H$3,$C:$D,2,FALSE)),"",VLOOKUP (G4&H$3,$C:$D,2,FALSE))

Untested but looks like it should work. ;-)

"TheFarmer42" wrote:


Arghhh!

Just when i thought it was all hunky dory!

Pivot table method:
In the data i am now applying it too, the pivot table is too large to
fit on one worksheet (whichever way i do it).
I could probably do two pivot tables after splitting the table, but it
doesn't seem very neat.
It also alphabetised the column data as well, which is not desirable,
and my guess at a workaround is not very neat either.

&,Vlookup method:
There is not a data point for every possible combination of the data.
If there is no data, the default should be zero. Since the formula
returns N/A, i can't make an easy formula out of this. I am planning to
do a two step process - a first table with N/A's in it, and a second
table that uses info from the first table to put zeros instead of
N/A's. Neater solutions anyone?

Cheers,
TF


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Thanks Dominic, that works.
I'd tried isna() before, but it didn't work - my syntax gets a bit
dodgy too late in the day!


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

You're welcome Farmer.

Glad you got it worked out. Captain's vlookup was pretty slick.

"TheFarmer42" wrote:


Thanks Dominic, that works.
I'd tried isna() before, but it didn't work - my syntax gets a bit
dodgy too late in the day!


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

You may be interested (or not) in what I used the techniques learned
here for. It was to establish a method of taking old (legacy) Linear
programming models in the common MPS format and making them available
for editing and solving in Excel.

http://www.johnquiggin.com/rsmg/wordpress/?p=28

Cheers,
TF


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751

Complicated value lookup
 

Very nice. Looks like a big brain cramp.

Thanks for sharing.

"TheFarmer42" wrote:


You may be interested (or not) in what I used the techniques learned
here for. It was to establish a method of taking old (legacy) Linear
programming models in the common MPS format and making them available
for editing and solving in Excel.

http://www.johnquiggin.com/rsmg/wordpress/?p=28

Cheers,
TF


--
TheFarmer42
------------------------------------------------------------------------
TheFarmer42's Profile: http://www.excelforum.com/member.php...o&userid=34102
View this thread: http://www.excelforum.com/showthread...hreadid=538751