Cell to return corresponding text

[Goddess]

Hi, I have a table, where I need the cell to return the corresponding text based on the text I entered in another cell. However, the reference cell, in this example "car"& "van" are grouped as "others" in the data table.

I basically need the data in the yellow cells, B2:D3 to auto populate when I enter the data in cell B1:D1. Bus will have it's own unique colour code, whereas the rest of the vehicles will be grouped as "others" as they have the same colour code. Possible to do it?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		car	van	bus					Others	Bus
2		Blue	Blue	Blue-light					Blue	Blue-light
3		Green	Green	Green-dark					Green	Green-dark
4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=I2
C2:D3	C2	=I2

Thanks!
Irene

 

[Eric W]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J
1		car	van	bus					Others	Bus
2		Blue	Blue	Blue-light					Blue	Blue-light
3		Green	Green	Green-dark					Green	Green-dark
4
Sheet6

Cell Formulas
Range	Formula
B2:D3	B2	=INDEX($I$2:$J$3,0,IFERROR(MATCH(B1,$I$1:$J$1,0),1))
Dynamic array formulas.

 

[aarceng]

Similar to Eric W but by omitting the "row" argument in the INDEX function you can return the entire column.
You can also use Data Validation with the top row as the list so that input is restricted to those values, in which case you would need to add a column for "other".

 

[Goddess]

Hi both,

Thanks for the feedback!

 

[Goddess]

hi sorry, I have another scenario...

I need to match the cells in yellow to the corresponding numbers in the blue cells. I have the number in the blue cells (H2:N5). I need those numbers to be auto populated into the yellow cells (B4:D4, B7:D7 & B10:D100

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		van	car	bus
2									blue	country	green	country	orange	country
3		blue	blue	blue-light				others	2	japan	6	thailand	7	india
4		2	2	3				bus-light	3	japan	5	malaysia	1	UK
5								bus-dark	4	india	3	germany	9	Spain
6		orange	orange	orange-dark
7		7	7	9
8
9		green	green	green-dark
10		6	6	3
11
Sheet1

Cell Formulas
Range	Formula
B4	B4	=I3
C4	C4	=I3
D4	D4	=I4
B7	B7	=M3
C7	C7	=M3
D7	D7	=M5
B10	B10	=K3
C10	C10	=K3
D10	D10	=K5

Thanks!

 

[aarceng]

It looks like you're trying to do the following.

What is the purpose?
Looking at the table H2:N5 it seems to me it could be better organised (and I'm assuming on of those 3's should be an 8).

 

[Goddess]

It looks like you're trying to do the following.
View attachment 112411
What is the purpose?
Looking at the table H2:N5 it seems to me it could be better organised (and I'm assuming on of those 3's should be an 8).
View attachment 112413

Hi aarceng,

No, that's not what I'm trying to do. The numbers are random and not in sequence; it could have numerous decimal points too. The table is organised as it is because there are other corresponding data that I did not include here.

I need the numbers in the yellow cells to auto populate whenever I change the data in row B1:D1 (van, car, bus) and the cells above the yellow cells (B to D, row 4, 7 & 10).

For further context, only Bus will be colour coded with additional light or dark, whereas the other vehicles will just have the colour. Country is irrelevant in this context; I'm adding it in as there's a column in my data sheet and I'm not sure if it will affect the formula if I removed it from this scenario.

Thanks!

 

[Burrgogi]

Like this?

=INDEX($I$2:$J$3,0,IFERROR(MATCH(B1,$I$1:$J$1,0),1))

Hi Eric!

I'm trying to improve my knowledge with formulas & functions and I was wondering if you could explain to me how this works exactly. When I saw the OP post her question, I played around with INDEX and MATCH nested formula but could not make it work. I never thought of using IFERROR function. I did a search online for an explanation of IFERROR function but how does this all work together?

 

[Cubist]

-delete-
solution would not work in 2021.

 

[Eric W]

Hi Eric!

I'm trying to improve my knowledge with formulas & functions and I was wondering if you could explain to me how this works exactly. When I saw the OP post her question, I played around with INDEX and MATCH nested formula but could not make it work. I never thought of using IFERROR function. I did a search online for an explanation of IFERROR function but how does this all work together?

It's pretty simple, if you're familiar with the functions.

=INDEX($I$2:$J$3,0,IFERROR(MATCH(B1,$I$1:$J$1,0),1))

The MATCH part looks for the B1 value in the I1:J1 range, and returns the position of the matching value. Then the INDEX function takes the I2:J3 range, and lets you select the part of the range you want. The results of the MATCH go in the column parameter, and if you put a 0 in the row parameter (or omit it like aarceng did), then INDEX will return all the rows.

If MATCH cannot find a match for B1, it returns an error. Then the IFERROR function kicks in and replaces the error with whatever you put in the second parameter, 1 in this case. So that if there is no match for B1, it will default to the first column of the table.