vlookup based on 2 criteria and return value from 4 different columns

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Hi all,

I have this following table :

NameAreaIn TimeApproved KM
xyzSinhagad Road6:1549
abcBibewadi9:1542
defSukhsagar Nagar9:15
hijWakad9:1565
klmPimple Saudagar9:15
nopAundh9:15
qrsPoud Road9:1546
tuvPoud Road9:15
wxyErandwane9:15
ghiShukravar Peth9:15

<tbody>
</tbody>

I use the following to get the approved km column

=VLOOKUP(B2,KM!$A$2:$G$281,4,0)

Now there are different number of persons in each duty,

like there is one in 6:15 then 2 in 9:15 then 3 in 9:15
and again 4 in 9:15.

(there are multiple entries like this)

Now in a different sheet named 'KM' I have the below table from
where I get the approved KM.
(there are many such locations, I have added only the ones which are in above table)

Location1 Associate2 Associate3 Associate4 Associate
BIBWEWADI41424344
ERANDWANE39404142
Shukravar Peth29303132
SUKHSAGAR NAGAR41424344
SINHAGAD ROAD49505152
POUD ROAD44454647
WAKAD63646566
Aundh46474849

<tbody>
</tbody>

Here I have to count number of employees and then change the column
number in the formula manually accordingly.

Can any one suggest me a suitable formula

thanks

Jack
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Jack

1. Based on the first 3 results, it looks to me like the fourth result should be 47, not 46. Is that correct? If not, why not?

2. First table 'Bibewadi' has different spelling to second table 'BIBWEWADI'. If that is not just a typo, please explain.

3. If I have understood correctly, this would be easier if the first table had a blank row just before the first row of actual data. Is that possible, given that it can be hidden?

Given all that, see if this helps.


Excel Workbook
ABCDEFG
1Location1 Associate2 Associate3 Associate4 Associate
2BIBWEWADI41424344
3ERANDWANE39404142
4Shukravar Peth29303132
5SUKHSAGAR NAGAR41424344
6SINHAGAD ROAD49505152
7POUD ROAD44454647
8WAKAD63646566
9Aundh46474849
KM






Formula in D3 copied down.
The #N/A result in my row 5 is due to the different spelling referred to above.

Excel Workbook
ABCD
1NameAreaIn TimeApproved KM
2
3xyzSinhagad Road6:1549
4
5abcBibewadi9:15#N/A
6defSukhsagar Nagar9:15
7
8hijWakad9:1565
9klmPimple Saudagar9:15
10nopAundh9:15
11
12qrsPoud Road9:1547
13tuvPoud Road9:15
14wxyErandwane9:15
15ghiShukravar Peth9:15
16
Sheet1
 

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Thanks Mr. Peter,

You are correct it should be 47 in the fourth
and the spelling is also a typing mistake.
Yes I can always add an empty row before the data

Only on one place the 'Poud Road' the formula is showing #VALUE!
=IF(C13="",VLOOKUP(B15,KM!$A$2:$G$281,3+MATCH(TRUE,INDEX(C16:C$16="",0),0),0),"")

everything is OK in the data even the spelling is correct
everywhere else the answers are showing fine as expected.
 

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
I think so the Index part of C$16 is creating a limit it this the problem
because there are more rows of similar entries below
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I think so the Index part of C$16 is creating a limit it this the problem
because there are more rows of similar entries below
The 16 needs to be at least one row below the last row of data in that table.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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?

Disable AdBlock

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
Top