Compare 3 cells and if equal then return 2 different values 1

[raboo2u]

I apologize in advance. I am new to creating formulas in Excel and need some help. So here goes, if I have not included enough information please let me know.


Here is a layout of my spread sheets.

Data
A B C D E F G
GS Marketing 2 99.9 01-Jan
GS Technical 10 95.2
GS GS 5 90.1
GS Billing 2 87.2

GS

B C D E K L M N
1
2 GS Billing
3
4 1-Jan ? ??
5 2-Jan

I need to fill in ? with the following:

1.

Data!A=GS!B2

and

Data!B=GS!K2

2.

In order to determine where to put the data from
the Data WS, there is a date. The date will always
be on Data!G1. I need the formula
to find the date that matches Data!G1 in GS
column K. If the date on G1=GS!K:K then on the
same row GS!L(row that was determined)=Data!D(row
that is determined by the match in 1.)

The values I want are GS!L4=GS Billing(87.2).


Here is how I want it to end up. With the values
for GS!L and GS!N filled in automatically.

Data
A B C D E F G
GS Marketing 2 99.9 01-Jan
GS Technical 10 95.2
GS GS 5 90.1
GS Billing 2 87.2

Data
A B C D E F G
GS Marketing 1 96.9 02-Jan
GS Technical 6 92.2
GS GS 10 99.0
GS Billing 123 86.0

GS

B C D E K L M N
1
2 GS Billing
3
4 1-Jan 87.2 2
5 2-Jan 86.0 123

 

[bluedragon2]

OK, If I understand this correctly:

In GS L4, put:

=OFFSET(INDIRECT(ADDRESS(MATCH(K4,G1:G100),4,4)),3,-1)

and in N4 put:

=OFFSET(INDIRECT(ADDRESS(MATCH(K4,G1:G100),4,4)),3,-2)



[Blue]Blue[/Blue] [Dragon]

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

 

[rdodge]

First, to be able to find it easily, create an index column with the following formula in row 2, then fill down:

=A2&"-"&B2

Let's say you put the above formula in H2 and copied the formula on down.

In Cell L4, put:

=INDIRECT("D" & MATCH(B2 & "-" & K2,INDIRECT("Data!H" & MATCH(Data!G1,GS!$K:$K,0) & ":H65533",0))

=INDIRECT("C" & MATCH(B2 & "-" & K2,INDIRECT("Data!H" & MATCH(Data!G1,GS!$K:$K,0) & ":H65533",0))

Of course, I could continue on to make this more dynamic, but after it gets to a certain point, I turn to VBA coding to do more complex things cause of the efficiency issue comes into play.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[raboo2u]

Ok. Neither option works. I am lost completely as to what may be causing the errors. I tried the first suggestion from bluedragon2 and it returns an error #N/A.

The second example from rdodge the first part =A2&"-"&B2 works. But when I put the info into L4 it returns #REF! and for N4 I get the same error. I have no idea how to troubleshoot this.

Raboo2u

 

[bluedragon2]

opps, try:

=OFFSET(INDIRECT(ADDRESS(MATCH(K4,Data!G1:G100),4,4)),3,-1)

and

=OFFSET(INDIRECT(ADDRESS(MATCH(K4,Data!G1:G100),4,4)),3,-2)

[Blue]Blue[/Blue] [Dragon]

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

 

[raboo2u]

bluedragon2

These are returning values now, but not the correct values. It appears to be pulling the numbers from the same worksheet in C4 for L4 and I have no idea where it is pulling the number for N4. I can't find that number on any worksheet. Can you explain what exactly the formula's you include are supposed to do?

 

[bluedragon2]

I am running out the office here right now, but if you want to send me a small sheet with some example data on it, I could take a better look at whats going on. The data you provided above is a little askewed as far as lineing up.

My e-mail is bluedragon_2 at yahoo.com



[Blue]Blue[/Blue] [Dragon]

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

 

[rdodge]

The one thing about the MATCH function, if there is not an exact match (provided the 0 is put in as the last argument of the MATCH function), it will error to "#N/A", so you may want to test the MATCH function in a separate cell for debugging purposes. Sort order for an exact match is not necessary.

If there is no value provided for the last argument or it's the value of 1, it will find the largest value that is less than or equal to the value that's in the first argument (Reference range in the second argument must be in ascending order).

If there is a value of -1 for the last argument, it will find the smallest value that is greater than or equal to the value that's in the first argument (Reference range in the second argument must be in descending order).

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rdodge]

I will be leaving soon too, but if you like me to take a look, you can email me at ronald.dodge@cfgraphics.com

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[raboo2u]

Hi,

If I enter =MATCH(B2&"-"&K2,INDIRECT("Data!H:H&quot) it returns the correct row of 22 on the Data page where the data exists. If I enter =MATCH(Data!G1,GS!$K:$K,0) then it returns 18 which is the correct row on the GS page I want the value returned to. Then when I add the full code =INDIRECT("D" & MATCH(B2 & "-" & K2,INDIRECT("Data!H:H" & MATCH(Data!G1,GS!$K:$K,0) & ":H65533",0)) and I get #REF. I am not sure what the last part & ":H65533",0 of this formula is doing. It seems like that is the part that is causing the problem.

I would send the page except my work does not allow me to send things.

Thanks

Robin

 

[raboo2u]

Also, if I do

=MATCH(Data!G1,GS!$K:$K,0)&":H65533" I get 18:L which is the correct row column combination. When I add the ,0 I get #REF.

 

[Zathras]

... The date will always be on Data!G1...

I don't see how you expect this to work with formulas. How many "DATA" worksheets will you have? 7? 31? 365? Are all of the "DATA" worksheets in the same workbook? If so, then why the fancy footwork? Use simple formulas.

If the intent is to open the "GS" workbook, then open a (daily?) "DATA" workbook and do an update, it seems to me like you need to go the macro route. A simple macro can look at the date, find the appropriate line to update and update it.

Perhaps if you explained a little more about how this is all going to be used, someone can work on solving your problem instead of developing complex formulas. Unless, of course, you plan to have all of the "DATA" sheets linked to the "GS" sheet, in which case formulas could be used.

 

[rdodge]

=INDIRECT("D" & MATCH(B2 & "-" &
K2,INDIRECT("Data!H:H" & MATCH(Data!G1,GS!$K:$K,0) &
":H65533",0))

In the above formula, it will not work, cause your Indirect function will look at:

"Data!H:H22:H65536", which will not work. To correct this, change the part that you see:

"Data!H:H"

To:

"Data!H"

Give this a shot afterwards.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[raboo2u]

I apologize for the lack of clarity on this subject. I am very new and am not meaning to upset anyone or frustrate. I will only have one data sheet that will be replaced each day with new values. That is why the date will always be in G1 and will always be TODAY(). The data page will have the Dept in column a, the queue in column b, the srvc lvl in column c, and the volume in colum d. The information will not come in sorted and will not be on the same row each time. An example of a row would be Customer Service, Billing, 90%, 200. Another example would be Safety, Billing, 80%, 1000. The values in column a will have duplicates and column b will also have duplicates.

There are 3 other main sheets that will have to get the data from the data sheet. Each sheet will have a different dept, queue names may be the same in 2 different depts, srvc lvl, and volume. On each sheet the department cell will never change and there can be up to 8 queues that will not change. For each queue there is a row for the srvc lvl and volume for each day. There are a total of 8 sheets, but only 3 departments that I need to worry about. The other are graphs and such.

Since I am new to this I don't know what additional information you need. Sorry again for frustrating everyone. If this is going to be to hard to do I will forget about it. Thanks.

 

[raboo2u]

rdodge -

That change now returns #REF. I sent you an email with a generic xls workbook. The values had to be changed though.

Thank you so much for your patience. I really appreciate your help!

 

[rdodge]

I have emailed you back a copy of the file with the formulae in one of the worksheets. The formulae I used in L18 and N18 respectively are:

=IF(ISERROR(MATCH($B$2&"-"&K$2,INDIRECT("Data!H"&MATCH($K18,Data!$G:$G,0)&":H65536&quot,0)),"",INDIRECT(ADDRESS(MATCH($B$2&"-"&K$2,INDIRECT("Data!H"&MATCH($K18,Data!$G:$G,0)&":H65536&quot,0),4,,,"Data&quot)/100)

=IF(ISERROR(MATCH($B$2&"-"&K$2,INDIRECT("Data!H"&MATCH($K18,Data!$G:$G,0)&":H65536&quot,0)),"",INDIRECT(ADDRESS(MATCH($B$2&"-"&K$2,INDIRECT("Data!H"&MATCH($K18,Data!$G:$G,0)&":H65536&quot,0),3,,,"Data&quot))

I know these formulae may appear to be long, but I have actually created formulae that's about 3 or 4 times that length. Of course, after a certain point, it becomes time to think about taking a lot of that to VBA to make it more efficient.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[raboo2u]

rdodge

Thanks...that was what I was looking for. One problem is that when you change the date the data does not stay where it is. It is removed and the new value is put in. I need the data to stay in the cell even after the date is changed. Is there a way to do that?

 

[rdodge]

Had setup the formulae as if you were going to have additional data further down the Data page like how you showed an example of in your first post of this topic, or at least hinted at. However, if that is not what you had in mind, you can do one of the following:

Manually convert the formulae to values

Copy the range
Special Paste the same Range as Values

Setup VBA to do it for you based on the Date in the cell of "Data!G1"

Dim wshDept as Worksheet, lngDateRow as Long, wshCnt as Long, dteLookupDate as Date
Application.ScreenUpdating = False
wshCnt = Thisworkbook.worksheets.Count
dteLookupDate = Thisworkbook.Worksheets("Data&quot.Range("G1&quot.Value
For I = 2 to wshCnt - 1
Set wshDept = Thisworkbook.Worksheets(I)
On Error Resume Next
lngDateRow = wshDept.Range("K:K&quot.Find(dteLookupDate,,xlValues,xlWhole).Row
If Err.Number <> 0 Then
Err.Clear
Goto WshEnd
End If
With wshDept.Range(&quot;K&quot; & lngDateRow & &quot;:IV&quot; & lngDateRow)
.Copy
.PasteSpecial(xlValues)
End With
Application.CutCopyMode = False
WshEnd:
Next
Application.ScreenUpdating = True
ThisWorkbook.Worksheets(&quot;Data&quot.Range(&quot;G1&quot.Select


Note, this procedure would need to be between the lines of:

Public Sub <Name>()

and:

End Sub

Hopefully, this will help you out.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[raboo2u]

Thank you all for your help. The formula is working great!!!