 # Formula to bring back value based on criteria in a column plus 4 columns away

• Hoping for some help!

I trying to write a formula to bring back a value

essentially it needs to look down column "G" until it finds a specific criteria such as "Total Cases" then bring back the data held in the same row that "Total Cases" appears in column A

[TABLE="width: 200"]

[tr]

[TD="class: xl63, width: 200"][/TD]

[/tr]

[/TABLE]

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

index match should do that for you

=index(A:A, match("total cases", G:G, 0 ))

if total cases is in a cell and will change , then put the cell reference in
=index(A:A, match(cell, G:G, 0 ))

ETAF

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

Quote from etaf;701594

index match should do that for you

=index(A:A, match("total cases", G:G, 0 ))

if total cases is in a cell and will change , then put the cell reference in
=index(A:A, match(cell, G:G, 0 ))

Perfect thanks!

next i need!

the same as above but to bring back the data in column A than is 1 row below the matched data in column G • Re: Formula to bring back value based on criteria in a column plus 4 columns away

=index(A:A, match("total cases", G:G, 0 )+1)

ETAF

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

Quote from etaf;701598

=index(A:A, match("total cases", G:G, 0 )+1)

cool ...

next i need to add 3 or 4 matches in column g together and then divide them by data in column a which has been found using a different match in column g

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

just use the index/match and add together and divide as normal

= (index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )+1) )
/
( i
ndex(A:A, match("total cases", G:G, 0 )+1) + index(A:A, match("total cases", G:G, 0 )+1) )

obviously the "total cases" needs to change and all on one line - just split to show

ETAF

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

It gives back the wrong % it should be 169% but i get 11806%

=(INDEX('Raw Data Nov 13'!A:A,MATCH("Orange",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Mango",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Other",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Red",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Teal",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Black",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Brown",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
/
(INDEX('Raw Data Nov 13'!A:A,MATCH("Colours",'Raw Data Nov 13'!G:G,0))))

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

you need another bracket at the end of all the plus
excel does / and * first
so
+INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
/
(INDEX('Raw Data Nov 13'!A:A,MATCH("Colours",'Raw Data Nov 13'!G:G,0))

will be done first

you need

=(
INDEX('Raw Data Nov 13'!A:A,MATCH("Orange",'Raw Data Nov 13'!G:G,0))

+INDEX('Raw Data Nov 13'!A:A,MATCH("Mango",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Other",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Red",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Teal",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Black",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Brown",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
)
/

to do the + together

you may have the formatting of the cell to % and returning a number that may explain a 100 error

if still an error

put each formula in to a cell separately and see what result you get for every one
and then use a calc and check the answer

ETAF

• Re: Formula to bring back value based on criteria in a column plus 4 columns away

Brill thanks!

• Re: Formula to bring back value based on criteria in a column plus 4 columns away