CODEWITHSUNDEEP
excelexcel-formula
Gyana Prakash
Gyana Prakash

Reputation: 79

Excel Formula Vlookup the value by referring Row and Column

In the sheet1 i have a table called working days of the countries as shown in the below image.

enter image description here

In the Sheet2 i have 10 columns in that based on the country and month by referring the this table i am trying to populate the values, When i tried doing by Vlookup the first row alone getting populated, but in the second row the header from F1:T1 is getting changed to F2:T2 so rest of the cells showing as #NA.

enter image description here

Anyone can you please give a solution for my issue. Here is the formula i have used.

=VLOOKUP(I1,Sheet1!F2:T7,MATCH(Sheet2!M1,Sheet1!F1:T1,0))

Thanks in Advance.

Upvotes: 0

Views: 363

Answers (3)

virtualdvid
virtualdvid

Reputation: 2411

You are missing the symbol $ to lock the ranges, and the false condition to match exact values in the VLOOKUP.

It should be like:

=VLOOKUP(I1,Sheet1!$F$2:$T$7,MATCH(M1,Sheet1!$F$1:$T$1,0),0)

Or instead of VLOOKUP use HLOOKUP like:

=HLOOKUP(M1,Sheet1!$F$1:$T$7,MATCH(I1,Sheet1!$F$2:$F$7,0),0)

Upvotes: 2

Dakota Lynch
Dakota Lynch

Reputation: 179

In general, combining the INDEX and MATCH functions is a superior option to VLOOKUP. For example, =INDEX(Sheet1!F:F,MATCH(Sheet2!M1,Sheet1!F1:T1,0)). This allows you to go left-to-right or right-to-left as well.

Upvotes: 1

Chandrakant
Chandrakant

Reputation: 21

Snip for Index Match functions Using Vlookup won't work here because in the 2nd table you are repeating the country, unlike the first table. Use a combination of index match function, this is little trickier than the vlookup but it will fulfill your requirements.

Since I don't have the exact table you shared so I created a table on my end and sharing the snip here.

Upvotes: 0

Related Questions