Vlookup

[alexanderdeluna]

I wanted to know if there was a way that a vlookup or any other reference tool can reference back multiple fields instead of one, in order for example to find the average of those fields, thanks!

[daffodil11]

Do you mean like "Find all cells in Column B with the word 'Red', and then of those that have 'Red' average the values on the same row in Column C?"

[alexanderdeluna]

no like find all the words in column A containing the word red (which is only one), then return the values from column D through G in order to average column D through G on a different tab

[alexanderdeluna]

so i guess in theory the formula would look something like this (even though it does not work)

=vlookup(a1,tab2!a1:z20, (instead of referencing one column, reference 3 columns, 0)

[daffodil11]

=Average(IF(isnumber(search("red",$A$1:$A$50)),$D$1:$G$50))

This is entered as an array, using Control+Shift+Enter instead of just enter.

This finds the rows with "red" in a1:a50, then averages all rows and columns that correspond. If there is only 1 row with "red", it will only average that single line for D:G

averageif-arra.xlsx

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.