Is this a Lookup Function

Topic

New Reply

My actual spread sheet contains over 3000 cost centre codes in one worksheet. I need to use this worksheet to match the cost centre codes and populate my “Extract” worksheet with the names of these Cost Centres in Column D. Would this be a Lookup Function?

I have attached a small sample to demonstrate my issue.

Kerry

Reply | Quote

Replies

Yes a Vertical Lookup will work. In ‘EXTRACT ‘!D2:
=VLOOKUP(C2,’Cost Centres’!$A$2:$B$9,2,0)

Copy down the column

Steve

Reply | Quote

Hello Steve

Thank you for your reply. I cannot seem to make this work.
In my live version I have 766 possible cost centres that have to be searched through to match about 3000 rows in the Extract Worksheet. A cost Centre may be repeated in the Extract Sheet.

Reply | Quote

Hi Kerry

First of all, when using lookups or match, you must compare apples with apples, i.e. if your cost center value is ‘text’ in your extract, then it must match a text value in your source. Ditto, if it is a ‘numeric value’, then it must be ‘numeric’ in both source and destination.

If you are going to retrieve more than one value from the source lookup table, based on say, the cost centre code, then it is more efficient to use MATCH and INDEX to retrieve the values.

See attached file for example.

zeddy

Reply | Quote

Hi Steve

..you were quicker than me, again.

zeddy

Reply | Quote

An NA error indicates that the cost centre is not found in the list. it seems from your pic that the item in C2 is text and I suspect that in Col A of the Cost Centres that they may be numbers. It is important that the both match since the text “705014” is not the same as the number 705014.

You can Try:
=VLOOKUP(VALUE(C2),’Cost Centres’!$A$2:$B$9,2,0)

which will convert the value in C2 to a number. Or you can ensure that all are text or all are numbers.
Steve

Reply | Quote

Thanks Steve and Zeddy both answers taught me something and got my brain working again. I love excel but dont get to use it often enough.

This one is going to be a bit of a challenge.

I want to add another complexity to my workings and are hoping you can help. I have attached a sample.
In Column E of the “Extract” sheet I want to check colum A – with Sheet “Printers” for a match in columns A or D – where there is a match copy the contents of the matching cells of “printers” to the Extract sheet Colum E. If there are matches in both A and D then it will require two colums.

Kerry

Reply | Quote

I am confused, there seem to be NO examples of matching columns in Extract with Printers 1 time or 2 times. There are matches in Cost center. Could you explain a little more clearly? It would be nice to see what you want as a result if the row has NO matches, has 1 match and has 2 matches so we can work on replicating the results with a formula…

Steve

Reply | Quote

Steve I apologise for my dreadful example. I have taken a fair amount of time putting together a better example of my issue. It is tricky when you have to de-identify data and keep the file size small.

In the attachment I want to search “PC-Monitors-Phones” for matching info to copy over to “Extract”. I have included some records showing examples in the first few rows of “Extract.”

In “PC-Monitors-Phones” search columns A,B,C,D,E for a match to “Extract” column A. When there is a match copy over the information from “PC-Monitors-Phones” Columns G,H,I,J,K and L to the corresponding columns in “Extract” I,J,K,L,M,N,O. Where there is no match the cell should be left blank.

Your help is always greatly appreciated.

Kerry

Reply | Quote

I am still confused. I don’t see how the output arises from your example as some of those records in Extract output are not in the PC-Monitors-Phone records. DB2236 is in the recordset despite your example and PC-Monitors-Phone Cols F-L feed into I-o, not G-L. If there will be only 1 match in Cols A-E then you can create a “helper column” (can be hidden) to calculated the desired row. In P2 enter:

Code:

=MATCH(A2,INDEX('PC-Monitors-Phones'!A:E,0,ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!A:A,0))*1+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!B:B,0))*2+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!C:C,0))*3+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!D:D,0))*4+ISNUMBER(MATCH(A2,'PC-Monitors-Phones'!E:E,0))*5),0)

and copy it down the column. This will give the matching row or a #N/A error if there is no match in any of the columns. Then in I2:
=IF(ISNA($P2),””,OFFSET(‘PC-Monitors-Phones’!$A$1,$P2-1,COLUMN()-4))

Copy this to I2:O2 and then copy I2:O2 down the columns.

I get some of the results you have in the example, but not all, so you will have to check. I believe your example is incorrect, not my formulas, but walk me through the logic if I am mistaken, because if so, I am missing something fundamental.

Steve

Reply | Quote

Steve you are a marvel! I have had brain burn sorting this, but I think I have it right now. :huh:

You were right, my example was incorrect.

To help you get a picture of what I am trying to achieve I have been tasked with identifying all the assets in our department, which thousands of items. The sample I have given you is only a tiny part of the spreadsheet, but if I can get it working on this sample I can work it into my real data.

I have come into it when part of the work was done and not in a way I would have organised it. I created a master spreadsheet with all assets ie EXTRACT. The PC-Monitors-Phones spreadsheet was sent out to workers to identify these assets and put in their information. Sadly not much thought was given to how the information received back was going to be matched with the EXTRACT. To do this manually would take forever.

I have worked in your code and the example is attached. The only thing I dont understand is Column I on Extract (Ture or False). And a further thought is, how can a duplicate be identified?

Reply | Quote

If you expand the formula bar, you can see the problem in I2:
37076-I2-Formula

This is the same for all the formulas in Col I. For whatever reason, you pasted it into the cell twice and have essentially:
=Formula=Formula

Which since it is the same formula you are comparing it to, will give TRUE. You can edit I2, to just have the 1 formula and then copy it down the column or just copy the formulas from col J to col I to fix it.

Steve
PS, what do you mean by duplicate? and identifying it in what sense? When I used the term, I used it in the sense that a lookup would only be in 1 of the columns from A to E, it would not be in more than 1. If it appears in more than 1 then “found row” would be wrong since the “found column” would be wrong….

Reply | Quote