I am looking for a way to match info in Column A AND Column B, then return
the appropriate value from Column C from the same row.

Column A is "plant names" and could have 4 rows of "Holly". Column B is
"size" and would distinguish the 4 hollies by size: 1g, 5g, 7g, 10g. I want
a formula that will look up the plant name, find the correct size, and return
the value in that row from Column C ("price").

Column A is not alphabetized, and has 450-500 rows. Column B repeats the
same possible sizes throughout the column. (1g, 5g, 7g, 10g)

Is there a formula for that? Please help.

With plant name in F1 and size in G1, use:

=INDEX(C1:C20,MATCH(F1&G1,A1:A20&B1:B20,0))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I am looking for a way to match info in Column A AND
Column B, then return
the appropriate value from Column C from the same row.

Column A is "plant names" and could have 4 rows
of "Holly". Column B is
"size" and would distinguish the 4 hollies by size: 1g,
5g, 7g, 10g. I want
a formula that will look up the plant name, find the
correct size, and return
the value in that row from Column C ("price").

Column A is not alphabetized, and has 450-500 rows.
Column B repeats the
same possible sizes throughout the column. (1g, 5g, 7g,
10g)

Is there a formula for that? Please help.
.

Jason,
Thanks so much for your kind attempt to help me. I appreciate your reply.
I am slow on this program, so forgive my slow response. I have been working
on this since yesterday. I have tried your suggestion, but it is not working
for me. I keep getting #VALUE. I also tried to change it to include the
total table reference for index and a column number to return, but it also
returns a #VALUE. Unfortunately, I don't know enough to know what I don't
know. Below are my two attempts. Can you see my error?
I am making an automatic order form. 'Plant name' and 'size' to be matched
to a price are named in cells G15 and C15 respectfully. The table reference
is another workbook (Current Price Sheet) and looks like this:

- A B C
D E
(r16) Botanical Name Common Name Size Price
Comments
(r17) Shrubs
(r18) illex cornuta... Dwarf Burford Holly 1 2.85
13" x 7"
(r19) illex cornuta... Dwarf Burford Holly 3 7.75
27" x 24"
this table continues to row 500.

'Plant name' entered in G15 should be looked up in column B. 'Size' entered
in C15 should match column C. Once the row with both matches is found, I want
the price in Column D returned. Column B is not alphabetized.

Here is my attempt to try your suggestion:

=INDEX('[Current Price
Sheet.xls]Sheet1'!$D$18:$D$500,MATCH($G$15&$C$15,'[Current Price
Sheet.xls]Sheet1'!$B$18:$B$500&'[Current Price
Sheet.xls]Sheet1'!$C$18:$C$500,0))

it returns #VALUE

Here is my attempt to try your suggestion with some things I added:

=INDEX('[Current Price
Sheet.xls]Sheet1'!$B$18:$D$500,MATCH($G$15&$C$15,'[Current Price
Sheet.xls]Sheet1'!$B$18:$B$500&'[Current Price
Sheet.xls]Sheet1'!$C$18:$C$500,0),3)

this also returns #VALUE.

I have also included the 16th row (column labels) in the formulas, with the
same response.

What am I doing wrong? (Besides the obvious: trying to do something way over
my head:))

Sorry to be so difficult to help, and hope you are not sorry you got
involved. Any more help you can send my way is truly appreciated.

Miss S.





"Jason Morin" wrote:

With plant name in F1 and size in G1, use:

=INDEX(C1:C20,MATCH(F1&G1,A1:A20&B1:B20,0))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I am looking for a way to match info in Column A AND
Column B, then return
the appropriate value from Column C from the same row.

Column A is "plant names" and could have 4 rows
of "Holly". Column B is
"size" and would distinguish the 4 hollies by size: 1g,
5g, 7g, 10g. I want
a formula that will look up the plant name, find the
correct size, and return
the value in that row from Column C ("price").

Column A is not alphabetized, and has 450-500 rows.
Column B repeats the
same possible sizes throughout the column. (1g, 5g, 7g,
10g)

Is there a formula for that? Please help.
.

Okay, take 2 on the 'Current Price Sheet' Table:


A B C D E
Botanical N. Common N. Size Price Comments
SHRUBS
illex... Holly 1 2.85 13" x 7"
illex... Holly 3 7.75 27" x 24"

row 16 is titles for columns.
row 17 is blank except for A17

Hope this posts correctly this time.

Your advice was perfect. It works like a charm!
I went back and found my error. (my size entry had a 'g' after the numbers
in c15 causing no match. Silly. I removed the g's.)
Thanks for everything!


"Jason Morin" wrote:

With plant name in F1 and size in G1, use:

=INDEX(C1:C20,MATCH(F1&G1,A1:A20&B1:B20,0))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I am looking for a way to match info in Column A AND
Column B, then return
the appropriate value from Column C from the same row.

Column A is "plant names" and could have 4 rows
of "Holly". Column B is
"size" and would distinguish the 4 hollies by size: 1g,
5g, 7g, 10g. I want
a formula that will look up the plant name, find the
correct size, and return
the value in that row from Column C ("price").

Column A is not alphabetized, and has 450-500 rows.
Column B repeats the
same possible sizes throughout the column. (1g, 5g, 7g,
10g)

Is there a formula for that? Please help.
.