| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup and calculations
hello everybody. I have a question: is it possible to have excel to look up
more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
You can use MATCH and INDEX in that way
=INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Hello Bob, thanks for the prompt reply... and what about the calculation? is
it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Antonov,
That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Thanks Bob... I will try it tomorrow at the office... will let you know....
"Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Bob.... ehmmm... I think I am doing something wrong....as all I get if I
apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Antonov,
I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Hello Bob.... I really thank you for helping out.. but... I still get the
same error... All I've done is to change the sheets names and copy and paste the formula in cell J7, and commit it as you told.... I really cannot understand what I am doing wrong... "Bob Phillips" wrote in message ... Antonov, I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Can you send me a spreadsheet? Give it a Subject of Excel - Antonov -
VLOOKUP - Programming -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob.... I really thank you for helping out.. but... I still get the same error... All I've done is to change the sheets names and copy and paste the formula in cell J7, and commit it as you told.... I really cannot understand what I am doing wrong... "Bob Phillips" wrote in message ... Antonov, I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Bob, I sent it to you.... did you get it?
"Bob Phillips" wrote in message ... Can you send me a spreadsheet? Give it a Subject of Excel - Antonov - VLOOKUP - Programming -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob.... I really thank you for helping out.. but... I still get the same error... All I've done is to change the sheets names and copy and paste the formula in cell J7, and commit it as you told.... I really cannot understand what I am doing wrong... "Bob Phillips" wrote in message ... Antonov, I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Hi Antonov,
Yes, I got it. I have emailed you a further question. Regards Bob "antonov" wrote in message ... Bob, I sent it to you.... did you get it? "Bob Phillips" wrote in message ... Can you send me a spreadsheet? Give it a Subject of Excel - Antonov - VLOOKUP - Programming -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob.... I really thank you for helping out.. but... I still get the same error... All I've done is to change the sheets names and copy and paste the formula in cell J7, and commit it as you told.... I really cannot understand what I am doing wrong... "Bob Phillips" wrote in message ... Antonov, I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and calculations
Hello Bob... did you have any luck understanding what I was looking for?
"Bob Phillips" wrote in message ... Hi Antonov, Yes, I got it. I have emailed you a further question. Regards Bob "antonov" wrote in message ... Bob, I sent it to you.... did you get it? "Bob Phillips" wrote in message ... Can you send me a spreadsheet? Give it a Subject of Excel - Antonov - VLOOKUP - Programming -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob.... I really thank you for helping out.. but... I still get the same error... All I've done is to change the sheets names and copy and paste the formula in cell J7, and commit it as you told.... I really cannot understand what I am doing wrong... "Bob Phillips" wrote in message ... Antonov, I think this is what you want =INDEX(Sheet2!$G$1:$G$20,MATCH(Sheet1!$A$1&Sheet1! $I$1,Sheet2!$A$1:$A$4&Shee t2!$B$1:$B$4,0))*INDEX(Sheet2!$C$1:$C$4,MATCH(Shee t1!$A$1&Sheet1!$I$1,Sheet2 !$A$1:$A$4&Sheet2!$B$1:$B$4,0)) don't forget it is anj array, so commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Bob.... ehmmm... I think I am doing something wrong....as all I get if I apply the formula is "#N/A"... The formula has to go in cell J7. The match has to be between A1&I1 and sheet2!A1:A4&B1:B4. Now: the table in Sheet2 is as follows: (A1) CI (B1) VE (C1) 0.004 (A2) CI (B2) MI (C2) 0.003 so... say the result of the match is CI VE then I need the data in cell Sheet1!G1 to be multiplied by Sheet2!C1.... do you think it is possible? thanks again for your patience "antonov" wrote in message ... Thanks Bob... I will try it tomorrow at the office... will let you know.... "Bob Phillips" wrote in message ... Antonov, That gets the value so just add *other_cell_ref on the end. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... Hello Bob, thanks for the prompt reply... and what about the calculation? is it possible? "Bob Phillips" wrote in message ... You can use MATCH and INDEX in that way =INDEX(Sheet2!$D$1:$D$20,MATCH(Sheet1!$A$1&Sheet1! $C$1,Sheet2!$A$1:$A$20&She et2!$C$1:$C$20,0)) which is an array formula so commit with Ctrl-Shift-Enter. The MATCH uses the concatenated values to lookup into tow concatentaed ranges, which returns ajn index in the range(s). This is passed to INDEX to get the actual corresponding value in Column D. -- HTH RP (remove nothere from the email address if mailing direct) "antonov" wrote in message ... hello everybody. I have a question: is it possible to have excel to look up more cells (i.e. =vlookup(sheet1!$A$1and$C$1,sheet2!$A$1:$C$3) and if both values are equal then multiply what's in cell sheet2! by another cell in sheet1!? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup Calculations | Excel Worksheet Functions | |||
| Vlookup Calculations | Excel Worksheet Functions | |||
| N/A results on vlookup with time calculations | Excel Worksheet Functions | |||
| ! in Calculations | Excel Discussion (Misc queries) | |||
| Calculations | Excel Worksheet Functions | |||
Linear Mode
