Cross-Sheet Lookup Help

[karim92]

kiosk reg.PNGReg Report.PNG

Hey all:

So i'll try my best to explain this... Basically, what I'm having trouble doing is matching the site ID's and filling in column E for whatever the first instant date is. For example, site id 22112 in the images has it's first instance on 1/7/2014 in the other image... That being said, I'd like to fill in column E in the first sheet (Tech Launch date) with 1/7/2014 for that row.

Basically would like the first instance filled in column E for the site ID matching on the other sheet. Not sure if that makes any sense. Any help would be greatly appreciated.

Thanks!

[alansidman]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome (manually entered is ok) and how you arrived at that. (Exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you. Also, not all members can upload picture files (Company firewalls, etc.)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[karim92]

Hi! Sorry about that. Thank you for clarifying. Attached is the file.

Again, basically I want to take the first instance date from tab 1, and bring it back into column E on tab 2 (matching by site id)

Thank you all for your help. I really appreciate it.

[Saarang84]

You can use this as an ARRAY formula (confirmed by pressing Ctrl+Shift+Enter),

Please Login or Register  to view this content.

If date column extends beyond column Y you need to change it accordingly in the above formula. Refer workbook attached.

[karim92]

Saarang: This is really great. Thanks so much, man.
I have another file in which I want to do the same thing, but in it there are different numeric values for each row (see revised attachment). Is there a way to bring back the first date instance, regardless of what number is there? I see you are looking up the value "1", but what if I just want the first value?

[ChemistB]

I used this Arrayed formula
=INDEX(Sheet1!$B$1:$Y$1, MATCH(TRUE, ISNUMBER(INDEX(Sheet1!$B$2:$Y$21,MATCH(D2,Sheet1!$A$2:$A$21,0),)),0))
Does that work for you?

[karim92]

Doesn't seem to be working for me. Keeps altering the phone number section. Could you send me your worksheet with the formula in place?

Thanks for your time.

[ChemistB]

Here is the sheet with my formula in E2 of Sheet2

[Saarang84]

I have another file in which I want to do the same thing, but in it there are different numeric values for each row (see revised attachment). Is there a way to bring back the first date instance, regardless of what number is there? I see you are looking up the value "1", but what if I just want the first value?

More condensed version of my formula is as below (Use it as an array formula by pressing Ctrl+Shift+Enter) :

Please Login or Register  to view this content.

If there are no values filled in for a particular Site ID, the IFERROR function will capture it and return a blank value. You may need to change the column ranges on the worksheet as required in your original workbook.

[ChemistB]

To explain Arrayed formulas (to the best of my ability)
When you signify you want a formula to be an arrayed formula, signified by Excel with {} around the whole formula when you hit CNTRL SHFT ENTER, you tell Excel to assign extra memory to that formula because you are going to be doing some sort of intermediate calculations. With a large amount of data, this can significantly slow down your sheet.

As a simple example
{=SUM(IF(A1:A5>0, B1:B5)}
Normally the first argument in an IF statement is a single cell. Here we want it to look at a range so Excel needs to have extra memory to store the information for each "Sub -IF"
Let's say A1:A5 = 2, -1, 0, 5, 11 and B1:B5 = 1,2,3,4,5
So Excel Stores this information
=SUM(1, FALSE, FALSE, 4, 5) and gives 10
Try it without using CNTRL SHFT ENTER
Hope that helps.