finding an output from 5 variables in an array using exterior input user variables

**Allsort** · 08-09-2013, 09:20 AM

Hi,

I'll say upfront im still a beginner with excel but i've been given the task of finding an output cell (TCO2/a/ha) from a large array of tree data. I've condensed the array to make it easier to solve the logic however im still stuck (after 3 blazing hopeful attempts).

there are 5 variables: spacing, yield, period(age), species and management.

the user must state which tree they are interested in the TCO2 absorbtion of by filling all the data cells (5 cells).

a single value should be returned when all 5 search cells are filled.

any help or link to applicable problem would be much appreciated,

(first timer) Allsort.

**XOR LX** · 08-09-2013, 09:45 AM

Hi and welcome to the forum!

This formula will do what you want in cell F4:

=INDEX($H$15:$H$46,MATCH(D10&D4&D5&D6&D7,INDEX($C$15:$C$46&$D$15:$D$46&$E$15:$E$46&$F$15:$F$46&$G$15:$G$46,,),0))

However, it will only work if you first spend a little time cleaning up your data. The MATCH formula (which is being used in effect several times here) requires an EXACT match and, unfortunately, at present a lot of your data (e.g. in columns C and G) contains extra spacing ("BE " instead of "BE" is one example).

If you can remove these first then you should be fine.

Regards

**Allsort** · 08-09-2013, 11:10 AM

this is perfect, i'll just have to go through and find the best way to clear up the data,

thanks!

**XOR LX** · 08-09-2013, 11:16 AM

You're welcome.

You can highlight any columns in which you are sure there are no spaces that you want to keep, then use the Find and Replace tool, enter a single space in the Find What box, leave the Replace With box blank and then click on Replace All.

Regards

finding an output from 5 variables in an array using exterior input user variables

LinkBack

Thread Tools

Rate This Thread

Display

finding an output from 5 variables in an array using exterior input user variables

Re: finding an output from 5 variables in an array using exterior input user variables

Re: finding an output from 5 variables in an array using exterior input user variables

Re: finding an output from 5 variables in an array using exterior input user variables

Thread Information

Users Browsing this Thread

Similar Threads

Calculating Output Value with variable Input Variables

User input variables Code not working

How do you input user defined variables into an AutoFilter statement?

Input/Output text files fill in variables

[SOLVED] variables in footers; how to prompt user for input within macro

Bookmarks

Bookmarks

Posting Permissions