# Double VLOOKUP maybe needs a array formula

#### Fin Fang Foom

##### Well-known Member
Good Afternoon,

I'm trying to do a double Lookup . But the values are not in one column it could be coulmn C thru AF.

So I have this formula and it gives me a #N/A error:

=INDEX(Sheet1!\$C\$3:\$AF\$24,MATCH(1,INDEX((Sheet1!\$B\$3:\$B\$24=Sheet2!\$B\$2)*(Sheet1!\$C\$2=Sheet2!G5),0,1),0))
In G5 it should say 147.50%

And H5 should be 132.31%

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sheet1!\$C\$1:\$AF\$1 ---> Sheet1!\$C\$2:\$AF\$2

I did what you suggested now I'm getting a !REF# error.

This Formula is in G5:

=INDEX(Sheet1!\$C\$3:\$AF\$24,MATCH(1,INDEX((Sheet1!\$B\$3:\$B\$24=Sheet2!\$B\$2)*(Sheet1!\$C\$2:\$AF\$2=Sheet2!G5),0,1),0))
Calendar array.xls
BCDEFGHIJ
1
2Antonio Buenrostro
3September 2005
4SundayMondayTuesdayWednesdayThursdayFridySaturday
509-01-200509-02-200509-03-2005
6#N/A#N/A#N/A#N/A#REF!#N/A#N/A
Sheet2

Hi Fin Fang Foom:

If I understand you correctly, you need the following formula in cell G5 ...

=INDEX(Sheet1!\$C\$2:\$G\$15,MATCH(\$B\$2,Sheet1!\$B\$2:\$B\$15,0),MATCH(G\$5,Sheet1!\$C\$2:\$G\$2,0))

then copy this to cell H6, ...

as presented in the following:
Book1
ABCDEFGHI
1
2Antonio Buenrostro
3
4SundayMondayTuesdayWednesdayThursdayFridySaturday
509/01/200509/02/200509/03/2005
6147.50%132.31%
7
Sheet2

Thank You ,Yogi Anand

The formula is perfect. Now I understand my ranges were reverse.

The formula is perfect.

Fin Fang Foom said:

I did what you suggested now I'm getting a !REF# error.

This Formula is in G5:

=INDEX(Sheet1!\$C\$3:\$AF\$24,MATCH(1,INDEX((Sheet1!\$B\$3:\$B\$24=Sheet2!\$B\$2)*(Sheet1!\$C\$2:\$AF\$2=Sheet2!G5),0,1),0))

...

I didn't pay attention to the whole formula. You don't need an array formula for your retieval task: Rather, just an INDEX formula or a VLOOKUP formula:

=INDEX(Sheet1!\$C\$3:\$AF\$24,MATCH(B2,Sheet1!\$B\$3:\$B\$24,0),MATCH(G5,Sheet1!\$C\$2:\$AF\$2,0))

=VLOOKUP(B2,Sheet1!\$B\$3:\$AF\$24,MATCH(G5,Sheet1!\$B\$2:\$AF\$2,0),0)

BTW, it's better not to put up non-working formulas to describe the problem you want to solve. Instead just describe it along with the expected/desired result.

Thanks Aladin it works just fine.

Now I have another problem How to get the date that matches with the days of the week.

Look at the example below:
Calendar array.xls
ABCDEFGHI
109-01-2005Antonio Buenrostro
2September 2005
3SundayMondayTuesdayWednesdayThursdayFridySaturday
409-02-200509-03-200509-04-200509-05-200509-06-200509-07-200509-08-2005
5
Sheet3

How can we modified this formula:

=IF(G4="","",IF(MONTH(G4+1)<>MONTH(G4),"",TEXT(G4+1,"mm-dd-yyy")))

So I could put in C4 and copy accross to I4.

To get this result:
Calendar array.xls
ABCDEFGHI
109-01-2005Antonio Buenrostro
2September 2005
3SundayMondayTuesdayWednesdayThursdayFridySaturday
409-01-200509-02-200509-03-2005
Sheet2

Its all depends for me to input the date in cell A1 and everything could be link.

Replies
8
Views
269
Replies
2
Views
243
Replies
5
Views
390
Replies
0
Views
113
Replies
2
Views
338

1,202,901
Messages
6,052,445
Members
444,581
Latest member
naninamu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back