Match & offset or index/match?

[EEEEEE]

Hi,
Trying to match a value and return the a value offset to the left.

The values to search in are in the columns B to E (as per the table)
I want to return the Level in column C

I know two values (these come from another tab)
1. The row heading (Red names) 'Risk template'!F5
2. What I'm searching for (Searching in here) located on 'Risk template'!F6

I've tried using offset
=OFFSET(C22,MATCH('Risk template'!F5,<how do I tell it what column it should search in as this changes??>,0),0,-1,1)

I was thinking index/match but I couldn't seem to figure out where to start??

I set the data up like the below as they are the dependant drop down boxes (column B to E) used in the 'Risk Template'!

Hoping this makes sense - Thanks

Column C (This is what I want returned)	Column B	Column D	Column E
Row22: LEVEL	customer_stakeholder	Environment	Finance
R23: Minor	Searching in here	Searching in here	Searching in here
R24: Moderate	Searching in here	Searching in here	Searching in here
R25: Major	Searching in here	Searching in here	Searching in here
R26: Critical	Searching in here	Searching in here	Searching in here

 

[KRice]

I would probably use INDEX/MATCH for this:

=index($C$23:$C$26,match("specific phrase/item in quotes if text or reference to a cell that holds the search term", $B$23:$B$26,0))
This performs a search of B23:B26 looking for an exact match, and then the index value returned is applied to the array C23:C26 to return the corresponding value in column C.

To search for a different term/expression/value in column D, use the same formula, but change the item to be searched for (1st argument of MATCH) and change the search array in MATCH (2nd argument)...so,
=index($C$23:$C$26,match("specific phrase/item in quotes if text or reference to a cell that holds the search term", $D$23:$D$26,0))

and do the same adjustments for column E search.

 

[EEEEEE]

Hi KRice,
Thanks so much for your response.
So I have figured out your first step of Index or Match:
=INDEX(C23:C26,MATCH('Risk template'!F5,Customer_Stakeholder,0))

Now how do I include it to search through the other two columns "Environment and Finance" in the same formula, ie I want it look through three Columns B,C&D for the match at the same time.

Apologies if your second instruction explained this but I couldn't understand how to extend it??

Thanks

 

[KRice]

Oh...sorry. I misunderstood your first post. I thought you wanted to conduct separate searches looking for a specific match in only one column each. If you want to search for multiple matches, where all match conditions must be met at the same time, I would opt for a different approach:
=SUMPRODUCT($C$23:$C$26,($B$23:$B$26=item to match in col B)*($D$23:$D$26=item to match in col D)*($E$23:$E$26=item to match in col E))
Not having seen the items to match, I'm not sure how to advise. These items to match can either be cell references, numbers, or if straight text, then enclose in quotes.
This will search each column for a match and return either a TRUE or FALSE for each cell. Then those vertical arrays are multiplied together, coercing the T's and F's to 1's and 0's. Only T*T*T will give 1...all other combinations will be 0, and when that resultant array of 1's and 0's is multiplied by C23:C26, the result will be an array with only the C items that match all criteria. Will there be only one item in this array? If so , this should work.

 

[EEEEEE]

Sorry it was difficult to explain at the start at it seems I wrote the columns incorrectly. They should be C,D,E,F
I only need to match one item, in either column D23 to F26 and return the value from column C on the same line


Basically on the starting tab (Risk template'!F5) they select an option from a drop down box. (this being text, they are the items lists in D23 to F26 on the data tab)
In another cell on this starting tab I want it to return my search through my data tab D23 to F26 finding a match and returning the value on the same line from column C.

Does this help?

 

[KRice]

I'm not sure I follow you...this is why uploading a minimal working example with the XL2BB add-in is helpful. If the value you want to match is on the main tab at F5 and you want the corresponding value from the "Data" tab that satisfies the matching criteria, AND the match term is found only in column D, E, or F of the Data tab, then this should work...put this formula in the cell on the main tab that is to receive the Data tab column C content:
=SUMPRODUCT('Data'!$C$23:$C$26,('Data'!$D$23:$D$26=F5)+('Data'!$E$23:$E$26=F5)+('Data'!$F$23:$F$26=F5))
Let me know if you run into any issues or if any of my assumptions aren't correct.

 

[EEEEEE]

Thanks very much for responding again.
I just used an IF statement as a workaround(used your index match from above) It works fine, a little long winded as I actually have 10 columns to search but I need to move on


=IF('Risk template'!E5='data sheet'!$D$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Customer_Stakeholder,0)),IF('Risk template'!E5='data sheet'!$E$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Environment,0)),IF('Risk template'!E5='data sheet'!$F$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Finance,0)),IF('Risk template'!E5='data sheet'!$G$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,ICT_Information,0)),IF('Risk template'!E5='data sheet'!$H$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Legal,0)),IF('Risk template'!E5='data sheet'!$I$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Human_Resources,0)),IF('Risk template'!E5='data sheet'!$J$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,WH_S,0)),IF('Risk template'!E5='data sheet'!$K$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Property_Assets,0)),IF('Risk template'!E5='data sheet'!$L$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Reputation,0)),IF('Risk template'!E5='data sheet'!$M$22,INDEX('data sheet'!$C$23:$C$26,MATCH('Risk template'!F5,Service_Delivery,0)),"NOT FOUND"))))))))))

Thanks again, really appreciate your help.

 

[KRice]

I'm glad you found a work-around. Your formula clears up some confusion for me. If you want to try a shorter alternative, here is a mock-up of both sheets. Input (blue cell) and results (green cells) are on the main 'Risk template' sheet:

MrExcel20200516_b.xlsx
	D	E	F
1
2
3
4
5		find -->	ghi
6		results	R23: Minor
7		col:	Reputation
8
Risk template

Cell Formulas
Range	Formula
F6	F6	=INDEX('data sheet'!$C$23:$C$26,SUMPRODUCT({1;2;3;4}*('data sheet'!$D$23:$M$26=F5)))
F7	F7	=INDEX('data sheet'!$D$22:$M$22,SUMPRODUCT({1,2,3,4,5,6,7,8,9,10}*('data sheet'!$D$23:$M$26=F5)))

The formula references this 'data sheet'.

MrExcel20200516_b.xlsx
	C	D	E	F	G	H	I	J	K	L	M
22	Level	Customer_Stakeholder	Environment	Finance	ICT_Information	Legal	Human_Resources	WH_S	Property_Assets	Reputation	Service_Delivery
23	R23: Minor									ghi
24	R24: Moderate		abc
25	R25: Major					jkl
26	R26: Critical										def
data sheet

 

[EEEEEE]

Well this is awesome!! I'm sorry for not trying it earlier, was snowed under. I will try the XL2BB tool next time, I wasn't sure if I could access it from my work computer....
Thanks again so much, you are very clever and kind for continuing to figure it out.