Help Searching for a match that returns same text

Eggplant2016

New Member
Joined
Jan 20, 2019
Messages
14
I want to enter text in a cell and then if the text that i enter matches text found in a cell that is part of a list, then I want the next 2 columns over to populate with numbers that correspond to the matched text.

So A1 is "Blue". B1 is "10" and C1 is "50".
A2 is "Red", B2 is "15", and C2 is "40"
A3 is "Green", B3 is "20" and C3 is "60"

So i want to enter "blue" in A6 and have B6 and C6 populate with the values from B1 and C1 respectively.

If i enter "Green" into A6, i want B6 and C6 to populate with the values from B3 and C3.

But I want this original list of colours to include hundreds of options. Each with their own values in the adjacent columns.

Thank you for your help
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi & welcome to MrExcel
Something like


Excel 2013/2016
ABCDEFG
2Blue1050red1540
3Red1540
4Green2060
report
Cell Formulas
RangeFormula
F2=INDEX(B$2:B$4,MATCH($E2,$A$2:$A$4,0))
G2=INDEX(C$2:C$4,MATCH($E2,$A$2:$A$4,0))
 
Upvote 0
Try
=INDEX(B$2:B$4,MATCH($E2,$A$2:$A$4,0))+H2*1000
 
Upvote 0
Thats great , thank you.
Now I want to have multiple "blues and greens" etc in Column A and have column "F" not only match the first instance, but total all the corresponding figures in "B" that are next to a "blue" or whatever color.
 
Upvote 0
I would suggest that you start a new thread & explain EXACTLY what you want, rather than continually "moving the goalposts"
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top