VLookUp to extract data
 

I've created a spreadsheet to perform engineering calcs. My problem is that
I need to enter my data table by first selecting 1 criteria (to narrow the
choices to several rows), then selecting a second criteria (to further refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.

For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given species,
there may be 5 sizes available, i.e. 5 rows with the same species name in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data col3))
5. Finally, the data table is sorted in ascending order around column 1.

For the procedu
1. I use data validation in cell 1 to select wood species (SPF #1, SPF #2,
....) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6, ...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells 1 & 2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my data
range, then read over two columns for the desired result.

The procedure works for all combinations where the value in hidden cell 3
matches an entry in column 1 of the data table. But, if the concatenated
value of hidden cell 3 does not match any value in column 1, it selects an
arbitrary row and extracts the incorrect data value, instead of showing an
error or some indication that it can not be found.

--
I APPRECIATE YOUR HELP - MD ENGINEER

VLookUp to extract data
 

Try setting rhe range_lookup argument to FALSE or 0.

=VLOOKUP(A1,B1:D10,3,0)

Biff

"MD ENGINEER" wrote in message
...
I've created a spreadsheet to perform engineering calcs. My problem is
that
I need to enter my data table by first selecting 1 criteria (to narrow the
choices to several rows), then selecting a second criteria (to further
refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.

For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given species,
there may be 5 sizes available, i.e. 5 rows with the same species name in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data
col3))
5. Finally, the data table is sorted in ascending order around column 1.

For the procedu
1. I use data validation in cell 1 to select wood species (SPF #1, SPF #2,
...) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6, ...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells 1 &
2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my data
range, then read over two columns for the desired result.

The procedure works for all combinations where the value in hidden cell 3
matches an entry in column 1 of the data table. But, if the concatenated
value of hidden cell 3 does not match any value in column 1, it selects an
arbitrary row and extracts the incorrect data value, instead of showing an
error or some indication that it can not be found.

--
I APPRECIATE YOUR HELP - MD ENGINEER

VLookUp to extract data
 

Thanks a bunch Biff! It seems to do what I need.
--
I APPRECIATE YOUR HELP - MD ENGINEER


"T. Valko" wrote:

Try setting rhe range_lookup argument to FALSE or 0.

=VLOOKUP(A1,B1:D10,3,0)

Biff

"MD ENGINEER" wrote in message
...
I've created a spreadsheet to perform engineering calcs. My problem is
that
I need to enter my data table by first selecting 1 criteria (to narrow the
choices to several rows), then selecting a second criteria (to further
refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.

For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given species,
there may be 5 sizes available, i.e. 5 rows with the same species name in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data
col3))
5. Finally, the data table is sorted in ascending order around column 1.

For the procedu
1. I use data validation in cell 1 to select wood species (SPF #1, SPF #2,
...) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6, ...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells 1 &
2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my data
range, then read over two columns for the desired result.

The procedure works for all combinations where the value in hidden cell 3
matches an entry in column 1 of the data table. But, if the concatenated
value of hidden cell 3 does not match any value in column 1, it selects an
arbitrary row and extracts the incorrect data value, instead of showing an
error or some indication that it can not be found.

--
I APPRECIATE YOUR HELP - MD ENGINEER

VLookUp to extract data
 

You're welcome. Thanks for the feedback!

Biff

"MD ENGINEER" wrote in message
...
Thanks a bunch Biff! It seems to do what I need.
--
I APPRECIATE YOUR HELP - MD ENGINEER


"T. Valko" wrote:

Try setting rhe range_lookup argument to FALSE or 0.

=VLOOKUP(A1,B1:D10,3,0)

Biff

"MD ENGINEER" wrote in message
...
I've created a spreadsheet to perform engineering calcs. My problem is
that
I need to enter my data table by first selecting 1 criteria (to narrow
the
choices to several rows), then selecting a second criteria (to further
refine
choice to one of the previously selected rows), then read across to a
specific cell and select its value.

For my data table:
1. Column 2 contains the various wood species names.
2. Column 2 contains the various member sizes. So, for a given
species,
there may be 5 sizes available, i.e. 5 rows with the same species name
in
column 2, and 5 different sizes in column 3.
3. Column 4 contains a third bit of data that I am seeking.
4. Column 1 then contains the formula TRIM(CONCATENATE(data col 2,data
col3))
5. Finally, the data table is sorted in ascending order around column
1.

For the procedu
1. I use data validation in cell 1 to select wood species (SPF #1, SPF
#2,
...) from a range.
2. I use data validation in cell 2 to select member size (2x4, 2x6,
...)
from a range.
3. I use a hidden cell 3 to concatenate the selected values from cells
1 &
2.
4. I then use VLOOKUP on cell 3 to find its value in column 1 of my
data
range, then read over two columns for the desired result.

The procedure works for all combinations where the value in hidden cell
3
matches an entry in column 1 of the data table. But, if the
concatenated
value of hidden cell 3 does not match any value in column 1, it selects
an
arbitrary row and extracts the incorrect data value, instead of showing
an
error or some indication that it can not be found.

--
I APPRECIATE YOUR HELP - MD ENGINEER