Help on Multiple Vlook-up criteria

[nandhamnk]

Dear ALL,


I need to find out sales person based on the below criteria:

Below is the database with Customer,Quarter-wise sales person


Customer Q1 -FY2011 Q2 -FY2011 Q3 -FY2012 Q4 -FY2012

X Mr.A Mr.A Mr.B Mr.A

Y Mr.C Mr.C Mr.C Mr.C

Z Mr.D Mr.E Mr.F Mr.G


I need to find out who is the sales person as below:

X Q1-FY2011 - (Mr.A)Answer

Z Q2-FY2011 - (Mr.E)Answer


Request your help please!

 

[Krishnakumar]

Hi


try


=INDEX($B$2:$E$4,MATCH(H2,$A$2:$A$4,0),MATCH(I2,$B$1:$E$1,0))


where


H2 holds Customer

I2 holds Quarter

B2:E4 your data range.


Kris

 

[nandhamnk]

Thanks Kris It works.

 

[nazmul_muneer]

Dear nandhamnk,

You can try the formula

=VLOOKUP(A6,$A$1:$E$4,MATCH(B6,$A$1:$E$1,0))


Regards,


--Muneer

 

[nazmul_muneer]

Think your column heading row is the first row i.e. A1:E1

 

[nandhamnk]

Thanks Muneer.The above formula works well.

 

[nazmul_muneer]

Woooh! Solved

 

[nandhamnk]

Dear Muneer,


above vlookup formula is not working for the below case:


Currency Apr-11 May-11 Jun-11 Jul-11

USD 44.2150 45.0600 44.7000 44.1900

GBP 73.6025 74.3250 71.5525 71.9650

EUR 65.7425 64.8675 64.7400 63.1450


For EUR,Apr-11 it should be 65.7425


Can you please check and confirm!

 

[nazmul_muneer]

Please check the spelling of your column heading and Lookup value

Spelling should be same


--Muneer

 

[nandhamnk]

I copy pasted as special which is in heading,still it is not working.Can you please check in excel.If it is working for you,can you please upload excel sheet Please!

 

[nazmul_muneer]

Please check the formula =VLOOKUP(A6,$A$1:$E$4,MATCH(B6,$A$1:$E$1,0),0)

 

[nandhamnk]

It working.Thanks for the support.Whats wrong in the previous formula?

 

[bobhc]

Good day nandhamnk


You may find this upload of help, multi look up multi data returns


https://dl.dropbox.com/u/34893656/Multi_Vlookup-1.xlsx

 

[nazmul_muneer]

@Nandhamnk,

Sorry for made the mistake!

Syntax of VLOOKUP = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)


Actually i forgot to type the range_lookup "0" for completion VLOOKUP formula.

If range_lookup is not written, it takes true (1) on default.


what is range_lookup?


Range_lookup : A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:


If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information, see Sort data.


If FALSE (0), VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.


--Muneer

 

[nandhamnk]

Thanks Muneer.I understand.

Thanks for the support.

 

[nazmul_muneer]

Happy to help you