Should I use an Index or Vlookup?

[gbtwo]

I'm going to use the following Table below as an example. What kind of Index formula could I use that could look through the table and find instances of "Dog" and color "Red" and bring back the other values in the row?
[table="width: 500, class: grid"]
[tr]
[td]Animal[/td]
[td]Name[/td]
[td]Color[/td]
[td]Size[/td]
[td]State[/td]
[/tr]
[tr]
[td]Duck[/td]
[td]Daffy[/td]
[td]Blue[/td]
[td]2X[/td]
[td]Michigan[/td]
[/tr]
[tr]
[td]Mouse[/td]
[td]Mickey[/td]
[td]Red[/td]
[td]3X[/td]
[td]Ohio[/td]
[/tr]
[tr]
[td]Mouse[/td]
[td]Minnie[/td]
[td]Green[/td]
[td]3X[/td]
[td]Michigan[/td]
[/tr]
[tr]
[td]Dog[/td]
[td]Goofy[/td]
[td]Red[/td]
[td]2X[/td]
[td]Ohio[/td]

[/tr]
[tr]
[td]Dog[/td]
[td]Porky[/td]
[td]Green[/td]
[td]1X[/td]
[td]Georgia[/td]
[/tr]
[tr]
[td]Bunny[/td]
[td]Bugs[/td]
[td]Red[/td]
[td]4X[/td]
[td]Florida[/td]
[/tr]
[tr]
[td]Dog[/td]
[td]Pluto[/td]
[td]Red[/td]
[td]3X[/td]
[td]Texas[/td]
[/tr]
[/table]

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Copy formula down and across as needed. Change ranges to match your data.

Excel Workbook
	A	B	C	D	E	F
1	Animal	Name	Color	Size	State
2	Duck	Daffy	Blue	2X	Michigan
3	Mouse	Mickey	Red	3X	Ohio
4	Mouse	Minnie	Green	3X	Michigan
5	Dog	Goofy	Red	2X	Ohio
6	Dog	Porky	Green	1X	Georgia
7	Bunny	Bugs	Red	4X	Florida
8	Dog	Pluto	Red	3X	Texas
9
10	Animal	Dog
11	Color	Red
12
13	Name	Size	State
14	Goofy	2X	Ohio
15	Pluto	3X	Texas
16
Sheet

 

[paleolith]

As a Florida resident, I have to point out that Florida is a yellow dog state. 'scuse me, a yaller dog state.

I might approach this in a couple of ways. If I'm always searching for the same keys, and especially if the table is large (hundreds or thousands of rows), I would create a hidden column which concatenates all the key columns. Then I'd use Match to search that column for the combined key value, and use the returned value to Index the row of the table. (You could also use Vlookup in this case, by placing the hidden column to the left of the table. However, that requires using Vlookup once for each value you want to retrieve. Match is better for retrieving multiple values.)

If the search is complex -- for example if the user is allowed to specify any combination of one to four columns to use as search keys -- then I would go into VBA and write a loop to go through the table. This will probably perform fine for hundreds of rows. If the table gets into thousands of rows and the search is getting executed constantly (IOW not just when the user punches a button), then performance might be slow. In that case you would need to reconsider how to store and search the data.

Edward

PS: I don't doubt AhoyNC's proposal, but making changes to it -- for example, using a different number of key columns, or even adding rows to the table -- will require understanding what has to be changed. It does avoid VBA and hidden columns.