Index or match function

[Benjamin2008]

Hello All.

Hope you are well. and thank you for reading my issue.
I am having some issue regarding finding cell values matching them and bring the specific cell value i want for example

Column W (Printer Name) Column G ( Branch Name) Column U ( Region)
HP Laserjet London Europe
EPSON L800 Warsaw Eastern Europe
HP Laserjet Warsaw Easter Europe

I need Column W to match Column G and Column G to Match Column U and bring printer name on sheet1 (B5)

Thank you in advance

Kind regards

Ben

[alansidman]

In your file you indicate that the printer from column W or V should be inserted in column B. What if there are two printers, one in W and one in V. Should the second one go in Column C?

[Benjamin2008]

Hi Alan,

Thank you for your quick reply.
Thats another problem I have, however ,At the moment i am only trying to resolve printer 1(W) but if you know the way how to go about it than please advice me.
Thanks

Ben

[Moo the Dog]

I made a column to list combined printers (if there are more than one) at each individual location in column Z, then used this ARRAY formula to return the results:

Please Login or Register  to view this content.

Note: ARRAY formulas are confirmed by pressing CTRL+SHIFT+ENTER, not just ENTER.

You'll just have to change the C$3 to H$3 for the WARSAW printers.

See my attached revised sheet.

- Moo

[Benjamin2008]

I made a column to list combined printers (if there are more than one) at each individual location in column Z, then used this ARRAY formula to return the results:

Please Login or Register  to view this content.

Note: ARRAY formulas are confirmed by pressing CTRL+SHIFT+ENTER, not just ENTER.

You'll just have to change the C$3 to H$3 for the WARSAW printers.

See my attached revised sheet.

- Moo

Dear Moo,

If I want to expand this from Sheet2!$Z$9:$Z$700 whats the changes i need to do.I did normal way but only shows up to 7 items on the list.

Ben[/QUOTE]

[Benjamin2008]

Dear Moo,

If I want to expand this from Sheet2!$Z$9:$Z$700 whats the changes i need to do.I did normal way but only shows up to 7 items on the list.

Ben[/QUOTE]

[newdoverman]

Is this what you have in mind? In order to get two printers if there are two printers, the easiest way is with a helper column in Sheet2 at the end of the data rows. I chose column Y and concatenated columns W and X with a double space between them.

[Moo the Dog]

Sorry, I wasn't around at my computer all day... you could change the formula to this (remember, ARRAY!):

Please Login or Register  to view this content.

- Moo

[Benjamin2008]

Good Morning Guys
Special Thanks To Moo for helping finishing my Project.

Ok I can match the columns and transfer them on sheet1
as you can see on the Envanter sheet not all the users has printer installed on their computers (envanter sheet Column W) and has empty cells from w9 to w700
Current formula Works fıne how ever ıf the cell ıs empty on envanter sheet Column W on sheet1 ıt puts down 0(Zero)(see sheet1) looks for the next cell value.
I would lıke to avoıd empty cells and when I ınsert the formulas on sheet1 ı need all values below eachother(nO empty cells to seen on sheet 1

Thank you
Ben

[azumi]

=IFERROR(IF(INDEX(Envanter!$W$9:$W$700,SMALL(IF(Envanter!$G$9:$G$700=C$10,ROW($1:$692)),ROW(1:1)))=0,"",INDEX(Envanter!$W$9:$W$700,SMALL(IF(Envanter!$G$9:$G$700=C$10,ROW($1:$692)),ROW(1:1)))),"")

Put in B13 and copied own

Azumi

[Benjamin2008]

=IFERROR(IF(INDEX(Envanter!$W$9:$W$700,SMALL(IF(Envanter!$G$9:$G$700=C$10,ROW($1:$692)),ROW(1:1)))=0,"",INDEX(Envanter!$W$9:$W$700,SMALL(IF(Envanter!$G$9:$G$700=C$10,ROW($1:$692)),ROW(1:1)))),"")

Put in B13 and copied own

Azumi

Hi Azumi

Thanks for the reply

any chance of hiding empty cells on sheet 1 ın between B13: B700 ....Only dısplay the cell with value

Thanks
Ben

[Benjamin2008]

Hi Guys
any chance of hiding empty cells on sheet 1 ın between B13: B700 ....Only dısplay the cell with value

Thanks
Ben

[azumi]

You mean like this, remove the blanks?

Azumi

[Benjamin2008]

You mean like this, remove the blanks?

Azumi

Azumi

You are a LEGEND my friend. Thank you so much for finishing my last headache.

Kind Regards

Ben

[azumi]

Im glad your problem solved and nice feebcak too...

Azumi