Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup
I have my array in two columns: COLUMN B COLUMN C COLUMN D F01 .065 .05 F02 .10 .08 F03 .08 .075 ETC.... (column B has F01, column C has .065, column D has .05........the spacing didn't come out right when I posted) My vlookup =VLOOKUP(B50,B$59:C$77,2,1) works fine at the moment. If I input F01 into cell B50...then the vlookup finds .065 and puts it in the appropriate cell. However, I need to add another criteria to my vlookup. I need the function to look at the first two numbers of another cell for example: cell D37 has input 0412359 with 04 standing for the year 2004 and pull the figures from Column D. 2003 values would fall in Column E and so on. Is this possible or should I keep on dreaming? Thanks in advance. -- saltH2O ------------------------------------------------------------------------ saltH2O's Profile: http://www.excelforum.com/member.php...o&userid=24835 View this thread: http://www.excelforum.com/showthread...hreadid=383875 |
#2
|
|||
|
|||
Try:
=VLOOKUP(B50,B$59:C$77,choose(value(left($D$37,2)) ,4,3,2,1,0,99)+3,1) It's untested... "saltH2O" wrote: I have my array in two columns: COLUMN B COLUMN C COLUMN D F01 .065 .05 F02 .10 .08 F03 .08 .075 ETC.... (column B has F01, column C has .065, column D has .05........the spacing didn't come out right when I posted) My vlookup =VLOOKUP(B50,B$59:C$77,2,1) works fine at the moment. If I input F01 into cell B50...then the vlookup finds .065 and puts it in the appropriate cell. However, I need to add another criteria to my vlookup. I need the function to look at the first two numbers of another cell for example: cell D37 has input 0412359 with 04 standing for the year 2004 and pull the figures from Column D. 2003 values would fall in Column E and so on. Is this possible or should I keep on dreaming? Thanks in advance. -- saltH2O ------------------------------------------------------------------------ saltH2O's Profile: http://www.excelforum.com/member.php...o&userid=24835 View this thread: http://www.excelforum.com/showthread...hreadid=383875 |
#3
|
|||
|
|||
Assumptions: 1) B1:M4 contains your data 2) C1:M1 contains your headers/labels, such as 2005, 2004, 2003, etc. 3) B2:B4 contains F01, F02, and F03 4) E37 contains your first criterion, such as F01 5) D37 contains your second criterion, such 0412359 Formula: =INDEX($C$2:$M$4,MATCH(E37,$B$2:$B$4,0),MATCH(LEFT (D37,2),RIGHT($C$1:$M$1,2),0)) ..confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges accordingly. Hope this helps! saltH2O Wrote: I have my array in two columns: COLUMN B COLUMN C COLUMN D F01 .065 .05 F02 .10 .08 F03 .08 .075 ETC.... (column B has F01, column C has .065, column D has .05........the spacing didn't come out right when I posted) My vlookup =VLOOKUP(B50,B$59:C$77,2,1) works fine at the moment. If I input F01 into cell B50...then the vlookup finds .065 and puts it in the appropriate cell. However, I need to add another criteria to my vlookup. I need the function to look at the first two numbers of another cell for example: cell D37 has input 0412359 with 04 standing for the year 2004 and pull the figures from Column D. 2003 values would fall in Column E and so on. Is this possible or should I keep on dreaming? Thanks in advance. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=383875 |
#4
|
|||
|
|||
=VLOOKUP(B50,B$59:E$77,(IF(LEFT(D37,2)="04",3,IF(L EFT(D37,2)="03",4,2))),FAL
SE) You must extend the size of your lookup range accordingly, note my use of B$59:E$77 Vaya con Dios, Chuck, CABGx3 "saltH2O" wrote in message ... I have my array in two columns: COLUMN B COLUMN C COLUMN D F01 .065 .05 F02 .10 .08 F03 .08 .075 ETC.... (column B has F01, column C has .065, column D has .05........the spacing didn't come out right when I posted) My vlookup =VLOOKUP(B50,B$59:C$77,2,1) works fine at the moment. If I input F01 into cell B50...then the vlookup finds .065 and puts it in the appropriate cell. However, I need to add another criteria to my vlookup. I need the function to look at the first two numbers of another cell for example: cell D37 has input 0412359 with 04 standing for the year 2004 and pull the figures from Column D. 2003 values would fall in Column E and so on. Is this possible or should I keep on dreaming? Thanks in advance. -- saltH2O ------------------------------------------------------------------------ saltH2O's Profile: http://www.excelforum.com/member.php...o&userid=24835 View this thread: http://www.excelforum.com/showthread...hreadid=383875 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |