vlookup in Matrix

[bilboy]

Dear all,

Hope this post finds you in good health.

My query is, Is it possible to do a vlookup for different combinations in a matrix.

eg: In the attached, vlookup to cover combinations A1, A2, A3, B1 etc..of both the matrices

Hope the query is clear.

Please excuse for any typo, typing via mob.

Stay healthy,
Bill boy

 

[NARAYANK991]

Hi ,

I think it's not clear.

The various combinations are A1 , A2 , A3 , A4 , B1 ,..., B4 , X10 , X11 , Y10 , Y11 , Z10 and Z11 , assuming that the data under S1 and P1 is not combined with the data under S2 and P2.

For each combination what should be returned by what ever formula is entered in B8 ?

Narayan

 

[bilboy]

Thanks Narayank991 for the response,

sorry for the confusion.

It need to just return the same value as a regular vlookup function, have changed the excel file for more clarity.

Formulae need to be in cell A9:A11,

Data will be randomly changed in data dump cells (B9:B11)

 

[NARAYANK991]

Hi ,

I am still not sure about all of this ; see the file and comment.

Narayan

 

[bilboy]

Excuse me Narayan for lack of clarity!!!

Consider the case as if product 1, 2, 3, 4 are available in store A & B, similarly, product 10, 11 in store X, Y, Z only.

If we get a list (data dump) with product sold in a store (Store ID&Product ID), we want to check whether the above mentioned combination is there or not.

 

[prasaddn]

Hi bilboy,

I did find something thats working, it looks very ugly formula though

=IF(IFERROR(IF(VLOOKUP(LEFT(B9,1),_S1,1,0)=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p1,1,0),IF(VLOOKUP(LEFT(B9,1),_s2,1,0)=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p2,1,0),NA())),"")="","",B9)

Take a look at at the attachment.

I have used, define names for range under Store1, Product1, Store2 and product2 as _s1, _p1, _s2 and _p2 respectively.

regards,
Prasad DN

 

[prasaddn]

Oops! some changes in formula is required:

Pls use:
=IF(IFERROR(IF(IFERROR(VLOOKUP(LEFT(B9,1),_S1,1,0),"")=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p1,1,0),IF(IFERROR(VLOOKUP(LEFT(B9,1),_s2,1,0),"")=LEFT(B9,1),VLOOKUP(MID(B9,2,LEN(B9))/1,_p2,1,0),NA())),"")="","",B9)

 

[bilboy]

Thanks a lot Prasad!!! i will tweak it a little to satisfy my requirement...

Also Thanks Narayan for bearing with me...

Gn and stay awesome..

 

[prasaddn]