LOOKUP table query. Please help. 2

[Darzza]

Hi,

I have 2 database:-

Spreadsheet (1) Is a list of part numbers ('A1' to 'A6000')
Spreadsheet (2) Is a complete list of all the different part numbers we offer (about 35000)

What I need to do is to look-up part numbers in spreadsheet (1) in spreadsheet (2). However I not only need to know that the part numbers are in spreadsheet (2) but I also need to take some other corresponding information from this spreadsheet.

Is it possible to do this automatically i.e. Look up all spreadsheet (1) part numbers in column A of spreadsheet (2) and generate the results (including columns 'e' and 'g') into a new spreadsheet.

Wow - I hope all that makes sense... cause you are a better man/woman than I if it does!

If you need any further information, please let me know.

Thanks very much for all your help,


P
T
Darzza

'Try fail, try again fail better....'

 

[bluedragon2]

You can do this with the VLOOKUP or DGET Functions. An example of the VLOOKUP function would look like:

=VLOOKUP(A1,Sheet2!A1:H20,7,0)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

The VLOOKUP formula will look for the value in shhet1 cell A1 in the table in sheet2's Column A and return the 7th column value or the G column value if it finds an exact match.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Darzza]

Thanks for your help.

Will this work even when the spreadsheets are separate?

Is there any chance you could explain what each parts of the formulae mean? Just so I know how to correspond it to my own spreadsheets.

Thanks once again.


P
T
Darzza

'Try fail, try again fail better....'

 

[xlbo]

Try looking at the help files in excel - then post back with the bits you do not understand

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Darzza]

Hello again,

I have tried to make this formulae work. To make life easier (or so I thought) I copied both of my sheets into a new workbook (so I am now working from one Workbook with two sheets in it)

The formulae I have is:-

=VLOOKUP(A3,Sheet2!A2:A33148+Sheet1!A2:A33148,5,0)

I have put this in sheet1 next to the other information. (ideally it would be in another spreadsheet.

The value I get is #VALUE! - so as advised I checked the excel help and they say:-

'If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value;'

But I thought this relates to my number 5 in the formulae which is more than 1...?!! What do I need to do?

My head is starting to hurt, roll on 5 o’clock.....

Thanks guys for your help.


P
T
Darzza

'Try fail, try again fail better....'

 

[Darzza]

Hi

Ok

new formulae =VLOOKUP(A3,Sheet2!A2:A33148,6,0)

Now the output I get is #REF!

Any ideas?

oh the pain....

Cheers.

P
T
Darzza

'Try fail, try again fail better....'

 

[bluedragon2]

=VLOOKUP(A3,Sheet2!A2:A33148+Sheet1!A2:A33148,5,0) is not valid.

For each part number on sheet one, you will have to have a seperate formula which can be easily done with cut and paste.

the array in your formula is not valid. On sheet 2, how many columns are in your table? Also, which column do you need the informtaion from? Also, where do you want the information to be displayed on sheet 1?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Darzza]

Hi

Ok on sheet 2 there are 31 columns (AE)

I need the information from column 5 (E) from this sheet. I would also like it to display the information it looked up too (i.e. the part numbe from column 1 (A).

I would like the information to be displayed in columns 8 and 9 of sheet 1.

Thanks for all your help.


P
T
Darzza

'Try fail, try again fail better....'

 

[xlbo]

The 2nd argument - your data range must contain enough columns to satisfy the 3rd argument (the range offset). eg. for your formula, your data range must contain at least 6 columns so that the formula can find the match and then move across 6 columns in the dataset - your data range should therefore be a MINIMUM of:
Sheet2!A2:F33148

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Please note - my post relates to the this formula:
=VLOOKUP(A3,Sheet2!A2:A33148,6,0)

rather than the latest one which was posted as I typed

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Darzza]

Geoff/bluedragon2,

Not for the first time today you have saved my bacon!

Many thanks.

P
T
Darzza

'Try fail, try again fail better....'