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

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

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

=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