Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup
Hello,
Could anyone help me... When I enter a vlookup formula, the first matching value is found and displayed, is there any way of getting the formula to look up the second or third values, e.g. to look at a trend over a particular week. I have been able to do this before somehow using these symbols {} but cannot remember what I done! Thanks for any suggestions, Willie |
#2
|
|||
|
|||
One way:
=INDEX(B1:B11,SMALL(IF(A1:A11=F3,ROW(A1:A11)-MIN(ROW (A1:A11))+1),X)) Array-entered (press ctrl + shift + enter), whe B1:B11 = range that contains the value to return A1:A11 = range that contains the value to look up F3 = holds the value to lookup X = nth instance to look up So, for example, this would find the 3rd instance of the value in F3 in A1:A11, and return the corresponding value in B1:B11: =INDEX(B1:B11,SMALL(IF(A1:A11=F3,ROW(A1:A11)-MIN(ROW (A1:A11))+1),3)) HTH Jason Atlanta, GA -----Original Message----- Hello, Could anyone help me... When I enter a vlookup formula, the first matching value is found and displayed, is there any way of getting the formula to look up the second or third values, e.g. to look at a trend over a particular week. I have been able to do this before somehow using these symbols {} but cannot remember what I done! Thanks for any suggestions, Willie . |
#3
|
|||
|
|||
Willie McL wrote:
Hello, Could anyone help me... When I enter a vlookup formula, the first matching value is found and displayed, is there any way of getting the formula to look up the second or third values, e.g. to look at a trend over a particular week. I have been able to do this before somehow using these symbols {} but cannot remember what I done! Thanks for any suggestions, Willie I assume you have different lookup values. If they are numbers, e.g., 5,3,6, you can array enter a hardcopied formula: Either =VLOOKUP({5,3,6},B1:D5,3,0) for a horizontal result or =VLOOKUP({5;3;6},B1:D5,3,0) for a vertical result. If you mean multiple occurrences of the same lookup value,Jason Morin has offered a solution (which I haven't tested). You might also consider the Vlookups function in the freely downloadable file at http://home.pacbell.net/beban for such a case. Alan Beban |
#4
|
|||
|
|||
Brilliant guys, thanks a lot!!!
"Alan Beban" wrote: Willie McL wrote: Hello, Could anyone help me... When I enter a vlookup formula, the first matching value is found and displayed, is there any way of getting the formula to look up the second or third values, e.g. to look at a trend over a particular week. I have been able to do this before somehow using these symbols {} but cannot remember what I done! Thanks for any suggestions, Willie I assume you have different lookup values. If they are numbers, e.g., 5,3,6, you can array enter a hardcopied formula: Either =VLOOKUP({5,3,6},B1:D5,3,0) for a horizontal result or =VLOOKUP({5;3;6},B1:D5,3,0) for a vertical result. If you mean multiple occurrences of the same lookup value,Jason Morin has offered a solution (which I haven't tested). You might also consider the Vlookups function in the freely downloadable file at http://home.pacbell.net/beban for such a case. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Vlookup #N/A error due to formatting | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |