0

I have a table with first column as primary key. Ex:

id  value1  value2
1       10      5
2       2       3
3       12      5
..

I also have a second list of id's I want to select, which can have repeated ids. Ex:

selectId
1
2
2
2
5
10
..

How can I "merge" the two tables (something like INNER JOIN) to obtain:

id  value1  value2
1       10      5
2       2       3
2       2       3
2       2       3
5       99      99
10      22      22
..

I tried using 'Microsoft Query' from Data > Extern Data to join the two tables. The problem is that it seems it cannot handle tables with more than 256 columns.

Thanks



UPDATE:

Thanks, VLOOKUP works as intended.
However one problem is that if the row was found but that corresponding column was blank, this function returns 0 (where I expected it to return an empty cell), and since zero is a valid value, I have no way to differentiate between the two (blank and zero)? Any help is appreciated..

Amro
  • 123,847
  • 25
  • 243
  • 454
  • where is your database? is it Access, SQL Server OR Oracle? What query have you written that throws this error? Does your table have 256 columns? – shahkalpesh Aug 05 '09 at 02:42

3 Answers3

3

If this is Excel -like the title says- just use vlookups. Not very relational, but that's the Excel way.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
2

Using the VLOOKUP function would get you the data in the layout you require.

If you are using Tables in Excel 2007, the formula would look like this based on the example below.

in cell B8 
=VLOOKUP([selectId],Table1,2,FALSE)

in cell C8
=VLOOKUP([selectId],Table1,3,FALSE)

Lookup screenshot http://img208.imageshack.us/img208/1/lookupz.png

Robert Mearns
  • 11,796
  • 3
  • 38
  • 42
0

It is not clear where you store your data, but it looks like you have this problem, described on Microsoft site: http://support.microsoft.com/kb/272729

mik
  • 1,575
  • 6
  • 22
  • 33
  • Like I said, I already have the excel files with multiple sheets, with one table per sheet. I rather trying to select a subset of rows from one table determined by the key column of that table, where the desired keys are stored in a second table. – Amro Aug 05 '09 at 05:39
  • The restriction of 256 cols was when I tried to use MS-Query to merge the tables and return the result... – Amro Aug 05 '09 at 05:40