Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I am using the vlookup function to check a table which may or may not have
multiple rows for the same value of the column I am using to select. So far, everything I have tried keeps giving me the first occurrence it finds. Do I need to add additional parameters or should I be using something other than vlookup? |
#2
![]() |
|||
|
|||
![]()
I'm sorry that I didn't answer your question.
vlookup will only show the first occurance . You will need something more. "Edith F" wrote: I am using the vlookup function to check a table which may or may not have multiple rows for the same value of the column I am using to select. So far, everything I have tried keeps giving me the first occurrence it finds. Do I need to add additional parameters or should I be using something other than vlookup? |
#3
![]() |
|||
|
|||
![]()
Edith F wrote:
I am using the vlookup function to check a table which may or may not have multiple rows for the same value of the column I am using to select. So far, everything I have tried keeps giving me the first occurrence it finds. Do I need to add additional parameters or should I be using something other than vlookup? If you have the functions in the freely downloadable file at http:/home.pacbell.net/beban available to your workbook you can use the VLookups function: =VLookups(lookup_value,Lookup_table,column_referen ce) array entered into enough vertical cells to accommodate the number of occurrences of lookup_value. Or, to avoid array entering: =Index(VLookups(lookup_value,Lookup_table,column_r eference), Row(A1)) filled down as far as required. Alan Beban |
#4
![]() |
|||
|
|||
![]()
Hi Edith,
maybe my function vlookupall() at http://www.sulprobil.com/html/vlookupall.html can help you. HTH, Bernd |
#5
![]() |
|||
|
|||
![]()
Alan Beban wrote...
.... If you have the functions in the freely downloadable file at http:/home.pacbell.net/beban available to your workbook you can use the VLookups function: .... Yes, but this could be done with built-in formulas. If the source range were named Tbl, the lookup value were in cell G1, and the topmost result in cell H1 with other results to appear below it in col H, the following formulas would work. H1: =VLOOKUP(G1,Tbl,2,0) H2 [array formula]: =IF(COUNTIF(INDEX(Tbl,0,1),G$1)ROW()-ROW(H$1), OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)), ROW()-ROW(H$1)+1),1,1,1),"") Fill H2 down as far as needed. |
#6
![]() |
|||
|
|||
![]()
Alan Beban wrote...
.... If you have the functions in the freely downloadable file at http:/home.pacbell.net/beban available to your workbook you can use the VLookups function: .... Yes, but this could be done with built-in functions. If the source range were named Tbl, the lookup value were in cell G1, and the topmost result in cell H1 with other results to appear below it in col H, the following formulas would work. H1: =VLOOKUP(G1,Tbl,2,0) H2 [array formula]: =IF(COUNTIF(INDEX(Tbl,0,1),G$1)ROW()-ROW(H$1), OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)), ROW()-ROW(H$1)+1),1,1,1),"") Fill H2 down as far as needed. |
#7
![]() |
|||
|
|||
![]()
Harlan Grove wrote:
Alan Beban wrote... ... If you have the functions in the freely downloadable file at http:/home.pacbell.net/beban available to your workbook you can use the VLookups function: ... Yes, but this could be done with built-in formulas. Indeed, as you demonstrated, though it's not clear why that would be desirable. The particular formula you provided is slower than the array entered VLookups formula when the number of recalculations on a sheet gets relatively large. I wonder how a user would test where the crossover in speed occurs so he/she could get some guidance on which works best in his/her application. I suppose just try them and see if there's a noticeable difference. Or, of course, if one just has a predisposition for built-in formulas without regard for efficiency, then there you have one. Alan Beban |
#8
![]() |
|||
|
|||
![]()
Alan Beban wrote...
.... The particular formula you provided is slower than the array entered VLookups formula when the number of recalculations on a sheet gets relatively large. .... There are situations in which Excel workbooks can't use any VBA, so it's good to know how to do certain tasks using no VBA. We may disagree about this, but IMO it's best to avoid VBA for anything that can be done compactly with built-in functions and defined names. Note the fuzzy term 'compactly'. On the other hand, if recalc performance is absolutely critical, better to use 2 formulas/cells per each result plus one extra formula/cell. G2: =ROWS(Tbl) H1: =VLOOKUP(G$1,Tbl,2,0) I1: =MATCH(G$1,INDEX(Tbl,0,1),0) H2: =INDEX(Tbl,I2,2) I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0) I guarantee you this will run recalc circles around your VLookups formulas. Benchmark results available upon request. |
#9
![]() |
|||
|
|||
![]()
Harlan Grove wrote:
Alan Beban wrote... ... The particular formula you provided is slower than the array entered VLookups formula when the number of recalculations on a sheet gets relatively large. ... There are situations in which Excel workbooks can't use any VBA, so it's good to know how to do certain tasks using no VBA. We may disagree about this, but IMO it's best to avoid VBA for anything that can be done compactly with built-in functions and defined names. Note the fuzzy term 'compactly'. On the other hand, if recalc performance is absolutely critical, better to use 2 formulas/cells per each result plus one extra formula/cell. G2: =ROWS(Tbl) H1: =VLOOKUP(G$1,Tbl,2,0) I1: =MATCH(G$1,INDEX(Tbl,0,1),0) H2: =INDEX(Tbl,I2,2) I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0) I guarantee you this will run recalc circles around your VLookups formulas. Benchmark results available upon request. Which of the formulas, if any, are to be array entered? Which get copied where to display the output? Alan Beban |
#10
![]() |
|||
|
|||
![]()
"Harlan Grove" wrote...
.... I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0) .... Oops, make that I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1 |
#11
![]() |
|||
|
|||
![]()
"Alan Beban" wrote...
Harlan Grove wrote: .... On the other hand, if recalc performance is absolutely critical, better to use 2 formulas/cells per each result plus one extra formula/cell. G2: =ROWS(Tbl) H1: =VLOOKUP(G$1,Tbl,2,0) I1: =MATCH(G$1,INDEX(Tbl,0,1),0) H2: =INDEX(Tbl,I2,2) I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0) .... I already mentioned I screwed up the I2 formula. It should be =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1 Which of the formulas, if any, are to be array entered? None. You couldn't test to be sure? You're not sufficiently familiar with Excel to know yourself? Just a rhetorical question for the benefit of other readers, and you prefer that device to simply stating none of them need to be entered as array formulas? Which get copied where to display the output? I considered my previous response an extension of my response before that, so implicit to drag the formulas in row 2 down until they return error values. I must endeavor to remember that you need everything explicit. |
#12
![]() |
|||
|
|||
![]()
Harlan Grove wrote:
"Alan Beban" wrote... Harlan Grove wrote: ... On the other hand, if recalc performance is absolutely critical, better to use 2 formulas/cells per each result plus one extra formula/cell. G2: =ROWS(Tbl) H1: =VLOOKUP(G$1,Tbl,2,0) I1: =MATCH(G$1,INDEX(Tbl,0,1),0) H2: =INDEX(Tbl,I2,2) I2: =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0) ... I already mentioned I screwed up the I2 formula. It should be =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1 Which of the formulas, if any, are to be array entered? None. You couldn't test to be sure? You're not sufficiently familiar with Excel to know yourself? Just a rhetorical question for the benefit of other readers, and you prefer that device to simply stating none of them need to be entered as array formulas? No. You gave me a screwed up formula for I2 and I was just trying to see why what you provided wasn't working as you suggested it would. Which get copied where to display the output? I considered my previous response an extension of my response before that, so implicit to drag the formulas in row 2 down until they return error values. I already did that with the only formula you provided for I2 and it produced garbage; so yes indeed, I and any one else trying to use what you provided needed something more explicit. Our fault, of course. I must endeavor to remember that you need everything explicit. No; just endeavor to test your stuff before you post it so you won't have to embarrass yourself by trying to shift the responsibility to me when it doesn't work. Alan Beban |
#13
![]() |
|||
|
|||
![]()
"Alan Beban" wrote...
.... No; just endeavor to test your stuff before you post it so you won't have to embarrass yourself by trying to shift the responsibility to me when it doesn't work. I admit my own mistakes. I screwed up the I2 formula, and I didn't test it (I knew the technique works, but I failed to add the I1 value to I2). However, did you make a clear statement that it didn't work? No, you babble some oblique crap about which formula to array-enter and which to copy where to get the results. |
#14
![]() |
|||
|
|||
![]()
Harlan Grove wrote:
"Alan Beban" wrote... ... No; just endeavor to test your stuff before you post it so you won't have to embarrass yourself by trying to shift the responsibility to me when it doesn't work. I admit my own mistakes. I screwed up the I2 formula, and I didn't test it (I knew the technique works, but I failed to add the I1 value to I2). However, did you make a clear statement that it didn't work? No, you babble some oblique crap about which formula to array-enter and which to copy where to get the results. How noble of you to admit your own mistakes while refusing to take responsibility for the confusion they directly caused. I was simply trying to exhaust the possibility that there was something I wasn't understanding about the use of your formulas before saying that it was the formulas themselves that were screwed up. But that seems to be a little too subtle for you to comprehend. |
#15
![]() |
|||
|
|||
![]()
"Alan Beban" wrote...
.... . . . I was simply trying to exhaust the possibility that there was something I wasn't understanding about the use of your formulas before saying that it was the formulas themselves that were screwed up. But that seems to be a little too subtle for you to comprehend. Unadulterated BS. The formula didn't work. Presumably you figured that out, but did you want to say that? Much less venture a fix? |
#16
![]() |
|||
|
|||
![]()
Harlan Grove wrote:
"Alan Beban" wrote... ... . . . I was simply trying to exhaust the possibility that there was something I wasn't understanding about the use of your formulas before saying that it was the formulas themselves that were screwed up. But that seems to be a little too subtle for you to comprehend. Unadulterated BS. The formula didn't work. Presumably you figured that out, but did you want to say that? Much less venture a fix? At the time I had *not* figured that out. I was still trying to figure out whether it was that the formulas could not work (which would imply that without so advising the users you posted without testing--not the most likely probability in my mind at the time), or that I was applying the formulas inappropriately; hence the questions about array entering and copying. I wouldn't consider venturing a "fix" unless and until I knew it was the formulas themselves and not my particular attempt to apply them that was the problem. But that's OK; rant on, it's instructive for the users. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |