Find input value in table with multiple criteria

[bwrengels]

Hi! I'm stuck with my Excel-sheet, and I hope someone can help me out! It's about the following issue:

I want to check whether a certain input value (in this case a certain species at a certain live webcam location) is present within the species' list of that specific location. So, my input value is "honey badger" (image 1; column E) at a certain location (webcam), in this case "DJ" (column C). Now, I want to check whether this combination is possible, and visualize this in the "Check"-column (F) by copying the specific value of that combination (species and webcam) from the table (image 2). How can I achieve this?

I hope someone can help me out!

Thank you in advance!

 

[DanteAmor]

Hi @bwrengels. Thanks for posting on the forum.

Check if any of these 2 options works for you:

Option 1:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Key	Date	Webcam	Time	Species	Check				Species	DJ	EP	NP	TE
2	1		DJ		Honey Badger					African Bush Elephant
3	2		DJ		Southern Giraffe					Hippopotamus
4	3		DJ		Burchell's Zebra					Southern Giraffe
5	4		EP		Cape Bushbuck					Steenbok	0			0
6	5		NP		African Bush Elephant					Cape Bushbuck
7	6		NP		Southern Giraffe					Honey Badger
8	7		TE		Steenbok	0				Burchell's Zebra
s1

Cell Formulas
Range	Formula
F2:F8	F2	=IF(INDEX($K$2:$N$18,MATCH(E2,$J$2:$J$18,0),MATCH(C2,$K$1:$N$1,0))="","",INDEX($K$2:$N$18,MATCH(E2,$J$2:$J$18,0),MATCH(C2,$K$1:$N$1,0)))

Option 2:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Key	Date	Webcam	Time	Species	Check				Species	DJ	EP	NP	TE
2	1		DJ		Honey Badger	0				African Bush Elephant
3	2		DJ		Southern Giraffe	0				Hippopotamus
4	3		DJ		Burchell's Zebra	0				Southern Giraffe
5	4		EP		Cape Bushbuck	0				Steenbok	x			x
6	5		NP		African Bush Elephant	0				Cape Bushbuck
7	6		NP		Southern Giraffe	0				Honey Badger
8	7		TE		Steenbok	x				Burchell's Zebra
s2

Cell Formulas
Range	Formula
F2:F8	F2	=INDEX($K$2:$N$18,MATCH(E2,$J$2:$J$18,0),MATCH(C2,$K$1:$N$1,0))

----------------------------------
Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[bwrengels]

Wow! That was quick, and it worked out perfectly! Thank you so much for your help. I'll use the XL2BB-function in the future for sure. Thanks again!