IF Statement Not Working Across Sheets

Hello All,
I need some help with a nested if statement.

I'm trying to pull a cells data from one sheet to another if it meets 2 requirements.
1, If Column A states "RF Module 1" and 2, If Column B states "RF Drive Power" to pull Column C.

Excel Sheet 1.PNG

This formula is working correctly if I keep it on the same sheet. But if I write the formula on a different sheet (which I need to do) I get a value error.

Formula that works: =IF(A180:A181=A180,IF(B180:B181=B180,C180))

Formula that does not work: =IF(Sheet1!A180:A181=Sheet2!B1,IF(Sheet1!B180:B181=Sheet2!A2,Sheet1!C180))
This gives me a #Value! error.

Is there a better way to do this or is something wrong?

Thanks,
Bdenn

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

I'm not sure how your first one works =IF(A180:A181=A180,IF(B180:B181=B180,C180)) as I copied it and put it in sheet1 and it returned value.
is this what you are looking for?
=IF(OR(Sheet1!A180=Sheet2!B1,Sheet1!A181=Sheet2!B1,Sheet1!B180=Sheet2!A2,Sheet1!B181=Sheet2!A2),Sheet1!C180,"")
BTW, you wrote

If Column A states "RF Module 1" and 2, If Column B states "RF Drive Power" to pull Column C.

But when I look at what is across from RF module 1 and RF drive power it isn't 6.3, it is 2575.9833.

ok, I did it with this...
=SUMPRODUCT((Sheet1!$A:$A=Sheet2!B$1)*(Sheet1!$B:$B=Sheet2!$A2),Sheet1!$C:$C)
dragged across and down. If you don't want to use whole columns you can limit the references to just the areas with the data.

It's a job done in seconds using Power Query.

Here's the M code:

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Column2	Account 1	Account 2	Account 3
2	A	1.6.10.3	3	4.5
3	B	1.6	3.1	4.6
4	C	1.7	3.2	4.7
5	D	1.8	3.3	4.8
6	E	1.9	3.4	4.9
7	F	2	3.5	5
8	G	2.1	3.6	5.1
9	H	2.2	3.7	5.2
10	I	2.3	3.8	5.3
11	J	2.4	3.9	5.4
12	K	2.5	4	5.5
13	L	2.6	4.1	5.6
14	M	2.7	4.2	5.7
15	N	2.8	4.3	5.8
16	O	2.9	4.4	5.9

Sheet: Sheet3

Let me know if you need help with this.

Or try lookup
B2
=LOOKUP(1,1/(Sheet1!$A$1:$A$45=B$1)/(Sheet1!$B$1:$B$45=$A2),Sheet1!$C$1:$C$45)

Have a look at Bo_Ry's suggestion.

I had not really worked with Power Query once ... Now I would not look back. But if you are not ready for it yet, that's fine. Just shout if you decide you want to look at it more closely in the future.