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

[jackt05]

Hi all,

I have this following table :

Name	Area	In Time	Approved KM
xyz	Sinhagad Road	6:15	49
abc	Bibewadi	9:15	42
def	Sukhsagar Nagar	9:15
hij	Wakad	9:15	65
klm	Pimple Saudagar	9:15
nop	Aundh	9:15
qrs	Poud Road	9:15	46
tuv	Poud Road	9:15
wxy	Erandwane	9:15
ghi	Shukravar Peth	9: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)

Location	1 Associate	2 Associate	3 Associate	4 Associate
BIBWEWADI	41	42	43	44
ERANDWANE	39	40	41	42
Shukravar Peth	29	30	31	32
SUKHSAGAR NAGAR	41	42	43	44
SINHAGAD ROAD	49	50	51	52
POUD ROAD	44	45	46	47
WAKAD	63	64	65	66
Aundh	46	47	48	49

<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

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	Location			1 Associate	2 Associate	3 Associate	4 Associate
2	BIBWEWADI			41	42	43	44
3	ERANDWANE			39	40	41	42
4	Shukravar Peth			29	30	31	32
5	SUKHSAGAR NAGAR			41	42	43	44
6	SINHAGAD ROAD			49	50	51	52
7	POUD ROAD			44	45	46	47
8	WAKAD			63	64	65	66
9	Aundh			46	47	48	49
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
	A	B	C	D
1	Name	Area	In Time	Approved KM
2
3	xyz	Sinhagad Road	6:15	49
4
5	abc	Bibewadi	9:15	#N/A
6	def	Sukhsagar Nagar	9:15
7
8	hij	Wakad	9:15	65
9	klm	Pimple Saudagar	9:15
10	nop	Aundh	9:15
11
12	qrs	Poud Road	9:15	47
13	tuv	Poud Road	9:15
14	wxy	Erandwane	9:15
15	ghi	Shukravar Peth	9:15
16
Sheet1

 

[jackt05]

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]

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]

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.