Formula for IF a cell in A1=C1, B1=D1, but more complicated

[Rugz06]

Hi All,

I need a way to auto populate a cell with a number from another worksheet if the field parameter matches.

Example (similar to below)
If a Cell within Column 1 (worksheet1) = a Cell within Column 1 (worksheet2), then Cell in Column 2 (W1, same row) = Cell from Column 2 (W2, same row)

Now this may be easy for the experts here, but what needs to be done is that if there are multiple entries as per attached, the average of the scores is given. The formula would need to work if there is 1 option or any number of multiple options. Example.JPG

Thanks

[Pete_UK]

Try this in B2 of Sheet1:

=AVERAGEIFS(Sheet2!$B:$B,Sheet2!$A:$A,A2)

then copy down.

Hope this helps.

Pete

[6StringJazzer]

Welcome to the Forum Rugz06!

I think you may want this in B2, and copy down:

Please Login or Register  to view this content.

But there are couple of things we need to clear up in your description.

First, note that you can attach a file here. You often get better responses if you attach an Excel file that has sample data and a sample of what you want results to look like.*

When talking about columns, please use letters, like Excel does, instead of numbers

Your example shows x, y, and z instead of the actual answers you would expect to see for this data. It would be helpful to show an example that is as realistic as possible.

These two lines seem to say different things:

If a Cell within Column 1 (worksheet1) = a Cell within Column 1 (worksheet2), then Cell in Column 2 (W1, same row) = Cell from Column 2 (W2, same row)

if there are multiple entries as per attached, the average of the score is given.

So the value for Pete in column B for Pete should be 56.666666667?

And what is an "option"? "need to work if there is 1 option or any number of multiple options"

___________________________
*The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Rugz06]

Thankyou for your warm welcome and quick responses.

The formula posted by Pete UK does work for the simple example posted above. Now I have played around with it to try to get it to suit my situation and have been unable to get it to work after about an hour or so.

I have tried attached the actual working sheet (with data removed) but am unable to for some reason. Might be my work computer settings. I cant post a link to the drop box due to being a new user, so your help in getting the correct columns and search fields would be greatly appreciated.. All i can do is post a few screenshots.

Now using the example above, My "Sheet1" = "Master List" and "Sheet2" = "F084 Subcontractor Review". I have highlighted the columns in yellow. I need the averages from F084 Column AG for all entries against that company in Column F to be brought across into Master List AQ against the Company listed in Column B

Data1.JPG
Data2.JPG
Data3.JPG
Data4.JPG

Thankyou.

[AliGW]

Please attach the workbook with DUMMY DATA included (not empty cells).

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), 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.

[Rugz06]

Thankyou.

Test File attached.

[Pete_UK]

Make sure that all the numbers in column AG of the F804 sheet are set to percentages - otherwise, you will be taking a average of the numbers 90 and 0.75 in this example.

Then you can use this formula in cell AQ2 of the Master sheet:

=AVERAGEIFS('F084 Subcontractor Review'!$AG:$AG,'F084 Subcontractor Review'!$F:$F,B3)

Set the format as percentage, and then copy it down as required.

Hope this helps.

Pete

[Rugz06]

It worked. Thankyou!

[Pete_UK]

Glad to hear it.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Rugz06]

Pete,
What if the number was replaced with just a word. And I didnt want the average because there would only be 1 entry in the column.

Basically the same condition if the cells match, but rather than the average of the numbers, I wanted it to equal that 1 cell.

So from the attached example, If a cell within !Master List! Column B matches !F084! Column F, then !master list! cell in that row = Column AH Cell

Thanks

[Pete_UK]

Use this in cell AQ3:

=IFERROR(INDEX('F084 Subcontractor Review'!AH:AH,MATCH(B3,'F084 Subcontractor Review'!F:F,0)),"")

then copy down.

Hope this helps.

Pete