Lookup tabel with 2 columns??

[mungovan]

Hi, Basically this is my problem:
I need to do a lookup on 2 columns that returns a value in the third column.

Example:
COLUMN1 COLUMN2 COLUMN3
3 4 1
3 6 0


So then in the cell:

If COLUMN1 contains a 3 and COLUMN2 contains a 4 then return 1
and

If COLUMN1 contains a 3 and COLUMN2 contains a 6 then return 0


Can this be done??

Kind Regards,
Declan

[Richard Buttrey]

Hi, Basically this is my problem:
I need to do a lookup on 2 columns that returns a value in the third column.

Example:
COLUMN1 COLUMN2 COLUMN3
3 4 1
3 6 0


So then in the cell:

If COLUMN1 contains a 3 and COLUMN2 contains a 4 then return 1
and

If COLUMN1 contains a 3 and COLUMN2 contains a 6 then return 0


Can this be done??

Kind Regards,
Declan

And what about if column 1 doesn't contain a 3, or column 2 doesn't contain either a 4 or 6?

You may need to clarify this last point, but assuming the default is neither 1 nor 0 but a null value, the following will work:

Please Login or Register  to view this content.

[mungovan]

Hello,

Thanks for the quick reply. Sorry I should have explained my problem a bit clearer:

I have a set of data that contains both X(COLUMN1) and Y(COLUMN2) co-ordinates and a binary data (COLUMN3, 1 or 0).

I need to be able to create a grid on the worksheet that corresponds to this information.

Example:
The data in table form would look like this:

X Y Binary
----------
1 3 1
3 6 0
4 5 1


Then using a lookup create a grid that looks something like this:

1 0 1 ....
0 0 1 ...
1 1 1 ...

Any ideas??

Cheers,
D

[Richard Buttrey]

Hello,

Thanks for the quick reply. Sorry I should have explained my problem a bit clearer:

I have a set of data that contains both X(COLUMN1) and Y(COLUMN2) co-ordinates and a binary data (COLUMN3, 1 or 0).

I need to be able to create a grid on the worksheet that corresponds to this information.

Example:
The data in table form would look like this:

X Y Binary
----------
1 3 1
3 6 0
4 5 1


Then using a lookup create a grid that looks something like this:

1 0 1 ....
0 0 1 ...
1 1 1 ...

Any ideas??

Cheers,
D

Hi,

Undoubtedly what you want to achieve can be done, but we need to know the complete set of rules. How do the 9 digits in the first matrix, map to the binary matrix?

It's not obvious from the data you've given.

I'm also a little confused since the example matrix seems at odds with your earlier statement. Originally you said:

If COLUMN1 contains a 3 and COLUMN2 contains a 4 then return 1
and

If COLUMN1 contains a 3 and COLUMN2 contains a 6 then return 0

implying that you wanted to calculate the digits in a third column based on two other columns. Now you seem to be suggesting that not only is there a mapping from two columns into 1, but also a mapping from a 3x3 matrix of digits to a 3x3 matrix of binary values.

If you indicate the complete set of rules I'm sure this will be very simple.

Rgds

[mungovan]

The information from the 2 matrix's doesn't match up, I was only using that as sample info. So in the actual case it would be:

X Y Binary
----------
1 1 1
1 2 1
1 3 0
1 4 1
2 1 0
2 2 0
2 3 0
2 4 1
.......


Then using a lookup create a grid that looks like this:

1 0 ....
1 0 ...
0 0 ...
1 1 ...
.........

Basically I want to put the value in the Binary column into the cell that matches the X and Y part.

Thanks,
D

[Richard Buttrey]

The information from the 2 matrix's doesn't match up, I was only using that as sample info. So in the actual case it would be:

X Y Binary
----------
1 1 1
1 2 1
1 3 0
1 4 1
2 1 0
2 2 0
2 3 0
2 4 1
.......


Then using a lookup create a grid that looks like this:

1 0 ....
1 0 ...
0 0 ...
1 1 ...
.........

Basically I want to put the value in the Binary column into the cell that matches the X and Y part.

Thanks,
D

Hi,

Sorry to labour this point, but I don't understand what the rule is for
a) working out the binary column. i.e. what rule determines that a 1&1 give a 1, a 1&2 give 1, a 1&3 give 0, etc., then

b) how do the rows of the 3x8 grid map to the Lookup grid, (which appears to be presumably a 2x8 grid). Or in another way, how does the first row of 1,1,1 become 1,0, and similarly the 1,4,1 row become 1,1 ?

Regards

[mungovan]

Hi,
Thanks for the reply.

Basically the first table is just the raw data that I'm using. So it's just sitting in columns in Excel. It has an X value, a Y value and then either a 1 or 0 is assigned it.

So, for example, the first record has an X value of 1, a Y value of 1 and a binary value of 1. That means that I want to place the binary value of 1 in the co-ordinate (X, Y) (1,1) below.

The second row then has X value of 1, Y value of 2 and binary entry of 1. That means I want to place 1 into the co-ordinate (1, 2) and so forth.

I've saved an image of what I'm trying to do on photobucket, this might help explain the problem a bit better:
http://i77.photobucket.com/albums/j5...ovan/Table.jpg

Cheers,
D

[mungovan]

Sorry,

Problem with the picture I originally posted, it was supposed to look like this:

http://i77.photobucket.com/albums/j5...an/Table-1.jpg

Cheers,
D

[Richard Buttrey]

Sorry,

Problem with the picture I originally posted, it was supposed to look like this:

http://i77.photobucket.com/albums/j5...an/Table-1.jpg

Cheers,
D

Hi,

OK I think I understand. I was assuming you wanted the binary values in column C to be calculated from the A&B values, whereas I now understand that they are given.

Assuming the Data is as you show it in the picture, first add a helper column D and enter the following formula in D1 and copy down.

Please Login or Register  to view this content.

Now in cell F3 enter the following formula and copy across to H3 and down to H6.

Please Login or Register  to view this content.

It uses the ROW() and COLUMN() functions so if you position the table elsewhere you'll need to adjust the two constants -5 & -2. Or if you position the data table elsewhere you'll need to change the references to columns C&D

HTH

[mungovan]

Richard,

Thanks a mill, it works perfectly!

I would have spent days on this trying to get it to work, applicate help.

Cheers,
D