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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top