compare a cell to a column and return a value from a third cell

[clivebillowes]

A

B

C

D

E

F

G

H

I

J

red

20​

clive

40​

if number in "I"="40"(b3)then return "blue" (A3) to this cell

green

30​

jan

20​

if number in "I"="20"(b1)then return "red" (A1) to this cell

blue

40​

paul

50​

black

50​

peter

30​

white

60​

pat

20​

pink

70​

colin

60​

​

 

[Cubist]

Book1
	A	B						H	I	J
1	red	20						clive	40	blue
2	green	30						jan	20	red
3	blue	40						paul	50	black
4	black	50						peter	30	green
5	white	60						pat	20	red
6	pink	70						colin	60	white
Sheet5

Cell Formulas
Range	Formula
J1:J6	J1	=INDEX($A$1:$A$6,MATCH(I1,$B$1:$B$6,0))

 

[clivebillowes]

=INDEX($A$1:$A$6,MATCH(I1,$B$1:$B$6,0))

Thank you.
I see it does not work on numbers with decimals, but I have used roundup to resolve.
I also need to do the same with rows looking at a value in a cell comparing with values in k22 TO T23, returning the value in k1 to k15
I used;
=INDEX($K$22:$T$23,MATCH(Q29,$K$1:$T$1,0))
but it returns #NA error

Book1
	A	B						H	I	J
1	red	20						clive	40	blue
2	green	30						jan	20	red
3	blue	40						paul	50	black
4	black	50						peter	30	green
5	white	60						pat	20	red
6	pink	70						colin	60	white
Sheet5

Cell Formulas
Range	Formula
J1:J6	J1	=INDEX($A$1:$A$6,MATCH(I1,$B$1:$B$6,0))

 

[Cubist]

1) Which Excel version are you using?
2) The provided formula only works for 1-dimension lookup.
3) Can you provide a more representative sample of your data? The roundup can perhaps be avoided.

 

[clivebillowes]

1) Which Excel version are you using?
2) The provided formula only works for 1-dimension lookup.
3) Can you provide a more representative sample of your data? The roundup can perhaps be avoided.

latest office 365 no version under help
after sending my reply I tried with decimal number again and it worked not sure what changed
I cant find a way to attach the spread sheet L2XBB is giving error
I had to screenshot to show the spreadsheet
only the formula in R29 is a problem now
Data is collected from various different sheets

 

[Cubist]

Try:

=TOCOL(IF(Q29=$K$22:$T$23,$K$1:$T$1,NA()),3)

PS: You should update your current Excel version in the Account Details for the best solution.

 

[clivebillowes]

I have sorted it all out
thank you very much
your assistance is greatly appreciated.