Three criteria irregular lookups

[BRISBANEBOB]

Without using helper columns, what is the best way to return a value from an array using three criteria from irregular data? The attached sample gives and example.

Thanks for any ideas.

[Palmetto]

Can you use this custom function?

3 Criteria VLOOKUP

[JBeaucaire]

First, fill in the holes in the range D7:D30. All the Plant 1 rows need to have a 1, so D7:D11 = 1, D12:D18 = 2, etc. You can color the font of those added numbers white if you really need them to "appear" blank.
Fix the value in F37, it should be JUN, not June.

Then put this formula in G36 and copy down:

=INDEX($F$7:$Q$30, MATCH($D36 & $E36, INDEX($D$7:$D$30 & $E$7:$E$30, 0), 0), MATCH($F36, $F$6:$Q$6, 0))


NOTE: Why does this feel like a homework assignment?

[rsdharan]

Mr JB, Certainly you are an expert in excel. can you please teach how to excel in excel vba.
rsdharan

[JBeaucaire]

I learn by helping people here on the forum. People ask questions and I analyze the answers given until I finally understand them...which sometimes takes a while.

Start trying to answer simpler questions...first on your own to see if the answer you come up with matches the answers provided later. As you match more and more, start offering answers of your own.

The surest way to know you've learned something is to see if you can show it to someone else.

[BRISBANEBOB]

Homework Assignment: I wish it was but I suspect at the age of 54 the days of homework are unlikely to reappear...

JB - I like the Index 'double' Match. But...is there any way to do it without having to fill in the missing data? That would be fine for a small data array, and I understand the missing data could be populated pretty quickly, but is there any way without touching the data spreadsheet? i.e. if you were doing the index or lookup from another sheet?

[JBeaucaire]

I've shown you the way I would do it. As for populating the missing numbers, that, too, can be done very quickly with a "fill down" trick I can show you, but I would never substitute a robust flat INDEX/MATCH formula with a cumbersome array-beast just to skip the fill down. I'd do the fill-down.

If you're interested in the quick fill-down trick, let me know.

[BRISBANEBOB]

For fill downs I usually go to a helper column and use (assuming I am starting in C3 as the helper)

"if(a5<>a4,+a5,+c3)"

Is there another way using Fill?

[JBeaucaire]

Heck yeah. Learned it right here on the forum.

1) Highlight the column of values including the blank cells.
2) Press F5 and select Special
3) Select Blanks and click OK
4) Press the equal sign =
5) Press UP ARROW one time
6) Press CTRL-ENTER

You've just inserted a formula into all those blank cells that displays the value of the cell above. To remove the formulas and leave the values, do a copy/edit/pastespecial/values.

[BRISBANEBOB]

Awesome...absolutely awesome. How come that's not well know. Another 'undocumented' feature?

Thanks again.

[shg]

Don't forget to convert theose formulas to values if the data is going to be rearranged in any way.