Provide a match from 2 cells to give a result from another

[leefarrell]

Hi I hope you can help with this is should be simple but I can not get it to
work.

I have a worksheet set out like this...
Sheet 1 layout

A - B - C -
1 Fixture - Mode - DMX Chan - (colum headings)
2 Robe - Mode 1 - "Value"
3 Robe - Mode 2 - "Value"
4 Robe - Mode 4 - "Value"
5 Vari - Mode 3 - "Value"

Colums A & B are both drop down menus using text validation. The source is in
sheet 3.

We have 40 fixture types each with between 2 and 5 mode settings, I want the
user to be able to select a fixture and what mode setting they want it to be
used in and it will then return a value from sheet 2.

Sheet 2 - Layout
A - B - C -
1 Fixture - Mode - DMX Chan - (colum headings)
2 Robe - Mode 1 - 20
3 Robe - Mode 2 - 22
4 Robe - Mode 3 - 33
5 Robe - Mode 4 - 45
2 Robe - Mode 5 - 54
3 Vari - Mode 1 - 24
4 Vari - Mode 2 - 28
5 Vari - Mode 3 - 36 Etc....

I have this speadsheet doing many other tasks but I can make the rest work,I
just can not get excel to see if cells A and B match and return a value from
sheet 2.

I would also like to have all of the rows blank unless data is in colum A, so
that i do not have pages of formular errors. idealy if a copy row above to
next free row feature is avalible it would be perfect as I will not have to
copy the formulars throughout the sheet.

I hope that this makes sense.

Many thanks in advance.

Lee

 

[Scoops]

Hi I hope you can help with this is should be simple but I can not get it to
work.

I have a worksheet set out like this...
Sheet 1 layout

A - B - C -
1 Fixture - Mode - DMX Chan - (colum headings)
2 Robe - Mode 1 - "Value"
3 Robe - Mode 2 - "Value"
4 Robe - Mode 4 - "Value"
5 Vari - Mode 3 - "Value"

Colums A & B are both drop down menus using text validation. The source is in
sheet 3.

We have 40 fixture types each with between 2 and 5 mode settings, I want the
user to be able to select a fixture and what mode setting they want it to be
used in and it will then return a value from sheet 2.

Sheet 2 - Layout
A - B - C -
1 Fixture - Mode - DMX Chan - (colum headings)
2 Robe - Mode 1 - 20
3 Robe - Mode 2 - 22
4 Robe - Mode 3 - 33
5 Robe - Mode 4 - 45
2 Robe - Mode 5 - 54
3 Vari - Mode 1 - 24
4 Vari - Mode 2 - 28
5 Vari - Mode 3 - 36 Etc....

I have this speadsheet doing many other tasks but I can make the rest work,I
just can not get excel to see if cells A and B match and return a value from
sheet 2.

I would also like to have all of the rows blank unless data is in colum A, so
that i do not have pages of formular errors. idealy if a copy row above to
next free row feature is avalible it would be perfect as I will not have to
copy the formulars throughout the sheet.

I hope that this makes sense.

Many thanks in advance.

Lee

Hi Lee

On sheet 2, put the values from columns A and B in column A (i.e.A2 is
"RobeMode1").

Delete column B.

In sheet 1, cell C2 enter

=if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B10,2,FALSE))

Adjust the lookup range (Sheet2!A2:B10) as required.

Regards

Steve

 

[leefarrell]

Thats fab many thanks, I need the user to be able to add many rows all with
the same formulars but differant products an modes, When i copy the formular
I have to change the "lookup Range" Is their a quick way to do this or do I
have to chage each row individulay?

Many Thanks

Hi I hope you can help with this is should be simple but I can not get it to
work.

[quoted text clipped - 42 lines]

Lee

Hi Lee

On sheet 2, put the values from columns A and B in column A (i.e.A2 is
"RobeMode1").

Delete column B.

In sheet 1, cell C2 enter

=if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B10,2,FALSE))

Adjust the lookup range (Sheet2!A2:B10) as required.

Regards

Steve

 

[Guest]

One way ..

In Sheet1,
Put in C2's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(OR(A2="",B2=""),"",INDEX(Sheet2!$C$2:$C$100,MATCH(1,(Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100=B2),0)))
Copy C2 down as far as required.

Adapt the ranges to suit the actual extent of data in Sheet2 before copying
C2 down

 

[Scoops]

Thats fab many thanks, I need the user to be able to add many rows all with
the same formulars but differant products an modes, When i copy the formular
I have to change the "lookup Range" Is their a quick way to do this or do I
have to chage each row individulay?

Many Thanks

Hi I hope you can help with this is should be simple but I can not get it to
work.

[quoted text clipped - 42 lines]

Lee

Hi Lee

On sheet 2, put the values from columns A and B in column A (i.e.A2 is
"RobeMode1").

Delete column B.

In sheet 1, cell C2 enter

=if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B10,2,FALSE))

Adjust the lookup range (Sheet2!A2:B10) as required.

Regards

Steve

Sorry Lee, my mistake for "live" typing, try:

=if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!$A$2:$B$10,2,FALSE))

The $ symbol fixes the range reference

Regards

Steve