Find value in third column

[amit96]

Hello everyone!

how can i find the value in -

1. IF in column AA the value in equal to the value in cell A1
2. AND in column AR the value is equal to "usd" or"euro" or"nok"
3. GIVE me the value in that line from column AC

 

[etaf]

you can use an ARRAY , OR - which will try and bring back 3 values

I'm assuming "usd" or"euro" or"nok" will not ALL match with AA in A1

=INDEX(AC:AC,MATCH(A1&{"USD","EURO","NOK"},AA:AA&AR:AR,0))
enter with Control +Shift + Enter - in 2013 version

OR

Each match separately
=INDEX(AC:AC,MATCH(A1&"USD",AA:AA&AR:AR,0))
=INDEX(AC:AC,MATCH(A1&"EURO",AA:AA&AR:AR,0))
=INDEX(AC:AC,MATCH(A1&"NOK",AA:AA&AR:AR,0))

So with the Nested - IFERROR

=IFERROR( INDEX(AC:AC,MATCH(A1&"USD",AA:AA&AR:AR,0)),
IFERROR(INDEX(AC:AC,MATCH(A1&"EURO",AA:AA&AR:AR,0)),
IFERROR(INDEX(AC:AC,MATCH(A1&"NOK",AA:AA&AR:AR,0),"Not Found")))

 

[amit96]

unfortantlly not working.. sorry

 

[etaf]

sorry about that

=IFERROR( INDEX(AC:AC,MATCH(A1&"USD",AA:AA&AR:AR,0)),
IFERROR(INDEX(AC:AC,MATCH(A1&"EURO",AA:AA&AR:AR,0)),
IFERROR(INDEX(AC:AC,MATCH(A1&"NOK",AA:AA&AR:AR,0),"Not Found")))

Missed a bracket out
This works
=IFERROR(INDEX(AC:AC,MATCH(A1&"USD",AA:AA&AR:AR,0)),IFERROR(INDEX(AC:AC,MATCH(A1&"EURO",AA:AA&AR:AR,0)),IFERROR(INDEX(AC:AC,MATCH(A1&"NOK",AA:AA&AR:AR,0)),"Not Found")))

BUT I would not use complete column references like AA , AR etc
I would use the range the data is in , or slightly more if the data is expanding
like
=IFERROR(INDEX(AC2:AC1000,MATCH(A1&"USD",AA2:AA1000&AR2:AR1000,0)) etc etc

as it slowed excel down on my Macbook quite a lot

Book5
	A																								Y	Z	AA	AB	AC	AD													AQ	AR
1	fred																								AC5
2																											john		AC2															usd
3	harry																								AC4		jill		AC3															euro
4																											harry		AC4															nok
5	mary																								AC9		fred		AC5															usd
6																											Chris		AC6															euro
7	paul																								Not Found		Mark		AC7															nok
8																											Janet		AC8															usd
9																											Mary		AC9															euro
10
11
Sheet2

Cell Formulas
Range	Formula
Y1,Y7,Y5,Y3	Y1	=IFERROR(INDEX(AC:AC,MATCH(A1&"USD",AA:AA&AR:AR,0)),IFERROR(INDEX(AC:AC,MATCH(A1&"EURO",AA:AA&AR:AR,0)),IFERROR(INDEX(AC:AC,MATCH(A1&"NOK",AA:AA&AR:AR,0)),"Not Found")))