VLOOK UP MATCH

[vmohan1978]

Dear all,

In sheet1, I have 2 columns date and currency ( USD, EUR, GBP , YEN) , in Sheet 2 I have 5 columns ( Date , USD, EUR, GGBP,YEN ) as mentioned Below

I need formula in Sheet 1 that matches date and currency wrt to sheet 2 ,

I am using formula =IF(B8="USD",VLOOKUP(A8,'1'!$B$1:$F$1274,2,0),IF(B8="EUR",VLOOKUP(A8,'1'!$B$1:$F$1274,4,0),IF(B8="GBP",VLOOKUP(A8,'1'!$B$1:$F$1274,3,0),"")))

I am getting #NA when the date in sheet1 is not matching with sheet2 , I need 0 if the dates are not matching in sheet 1 wrt sheet 2 .

Please do the needful


Example

Sheet1

A B

Date Currency Rate

1-Feb-13 USD Formula required (53.3238)

5-Feb-13 GBP 83.9655


IN Sheet 2


Date USD GBP EURO YEN


7-Feb-13 53.1445 83.2695 71.9355 56.7500

6-Feb-13 53.0855 83.1160 72.0165 56.5900

5-Feb-13 53.2945 83.9655 71.8773 57.7100

4-Feb-13 52.9730 83.1703 72.2060 57.1700

1-Feb-13 53.3238 84.5982 72.6296 57.8500

31-Jan-13 53.2890 84.2233 72.2325 58.6600

 

[Jai9]

Hi,


Try,


=IFERROR(INDEX(Sheet9!$A$1:$E$7,MATCH(Sheet8!A2,Sheet9!$A$1:$A$7,0),MATCH(Sheet8!B2,Sheet9!$A$1:$E$1,0)),0)


You have to replace Sheet8 with Sheet1 and Sheet9 with Sheet2.


Jai

 

[vmohan1978]

Jai ,


Thank you very much , it worked perfectly. I was trying only with VLOOKUP Function.

It saved my lot of time .

Once again Thank you very much .

 

[Jai9]

You are welcome Mohan !!


Jai