| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup for more than one cell
Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in
column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
You could try this *array* formula in B1 of Sheet2:
=INDEX(Sheet1!$C$1:$C$10,MATCH(1,(Sheet1!$B$1:$B$1 0=A1)*(Sheet1!$A$1:$A$10=" LIAB"),0)) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. After the CSE entry, copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sweetetc" wrote in message ... Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
Ray thanks for your quick response. I am not familiar with arrays, but sheet
1 has hundreds of lines of data and it appears your formula is calling for absolute values (if I am reading it right) That is why I was hoping that I could do look up that says vlookup. -- Thanks ETC "Sweetetc" wrote: Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
hi! why don't you try =index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0)) where c1 houses your lookup value? -via135 Sweetetc Wrote: Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=564006 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
I was hoping that I could do look up that says vlookup.
Vlookup won't work in this case. An alternative to the array formula**: =SUMPRODUCT(--(Sheet1!A$1:A$100="LIAB"),--(Sheet1!B$1:B$100=1103),Sheet1!C$1:C$100) ** This will only return the correct result if there is only one instance of LIAB 1003. Biff "Sweetetc" wrote in message ... Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
Even if I put the look up value in c1 Sheet 2 as LIAB 1103
Sheet #1 houses them in 2 different cells. How does this lookup work. -- Thanks ETC "via135" wrote: hi! why don't you try =index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0)) where c1 houses your lookup value? -via135 Sweetetc Wrote: Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=564006 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
yes..Biff is correct! -via135 Sweetetc Wrote: Even if I put the look up value in c1 Sheet 2 as LIAB 1103 Sheet #1 houses them in 2 different cells. How does this lookup work. -- Thanks ETC "via135" wrote: hi! why don't you try =index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0)) where c1 houses your lookup value? -via135 Sweetetc Wrote: Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=564006 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=564006 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for more than one cell
Let me give it a try
Thanks ETC "Biff" wrote: I was hoping that I could do look up that says vlookup. Vlookup won't work in this case. An alternative to the array formula**: =SUMPRODUCT(--(Sheet1!A$1:A$100="LIAB"),--(Sheet1!B$1:B$100=1103),Sheet1!C$1:C$100) ** This will only return the correct result if there is only one instance of LIAB 1003. Biff "Sweetetc" wrote in message ... Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in column A and # in Column B See Below A B C Aug-05 1103 44,023.151 Jul-05 1103 42,684.574 LIAB 1103 340,573.884 AGED 1103 1,235,569.497 TOTALS 1103 1,576,143.381 Aug-05 1265 365.497 Jul-05 1265 7,012.625 LIAB 1265 64,477.315 AGED 1265 174,469.632 TOTALS 1265 238,946.947 My new spreadsheet #@ has just numbers. A B 1103 1105 1106 How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet #1 and if it has the word LIAB in Column A then take the corresponding cell value in sheet #1 column C and put it into column B of Sheet #2 -- Thanks ETC |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| VLOOKUP using a cell calculated with NOW returns Error | Excel Worksheet Functions | |||
| VLookup resulting in a blank cell... | Excel Worksheet Functions | |||
Linear Mode
