Easy Question for you Excel Gurus - If? Match? Lookup?

[aware073]

Hi.. I have two little issues.

I first need a formula that will return an X if the rows in a column match a cell on a separate sheet
(If the vendor at top of Column E, sheet 2 is the vendor in rows of column L on sheet 1 for any of the part numbers listed on sheet 1 column A)

**Sheet 1 has part numbers with vendors listed a few columns over, sometimes if a part number has multiple vendors it will be listed more than once so that theres another line with the second vendor on it
**Sheet two has column A as the part number, and the vendors all listed with their own columns

I have to put an X in the part number row, vendor column on sheet 2 to show which part numbers we've gotten from which vendors.

Next, I have to put this new formula inside this formula

=IF(VLOOKUP(A6,'FE Inv with LPP'!A:L,10,FALSE)>0,"X"," ")

So that if the first formula returns a match it will go on with the if and vlookup to a return an X only if both are true..
Does that makes sense to anyone?

[mikeTRON]

Can you post a sample workbook?

[aware073]

Can you post a sample workbook?

Yes!! Thanks so much for replying!

[mikeTRON]

Can you post a sample workbook, and not a collection of images?

[aware073]

Will this work? Sorry I'm new to the forum.
Sample Spreadsheet.xlsx

I just need an X in the box under the vendors (row 1) in sheet 2 if we've purchased that product from them before (Columns A and L on Sheet 1).

[aware073]

Maybe the answer is an Index Match formula placed inside an "If>0=X" formula? Anybody???

[mikeTRON]

Sorry, so I hop on here between projects or meetings when I have a few minutes of down time so I have to digest the questions quickly in order to assist.

With that being said, I dont see Vendors anywhere on sheet 2.


Can you make a clear example and indicate what cell you are looking for a formula to produce this "X"?

[aware073]

The vendors are all listed across in Row 1 of the Sheet 2 spreadsheet, listed along the top.

In Sheet 2 I would like there to be an X in G3 if you go to Sheet 1 and have the vendor code or vendor (300003 from G1 or ABSOLUTE INNOVATIONS from G2) listed across from product 20000001 (Would be in Columns L or M).

[jjhayes]

Like this?

[aware073]

YES YES YES. Cant thank you enough, seriously. I've been at this for two days, I probably wouldn't have slept tonight

[aware073]

When I copy the formula into excel it keeps popping up my Documents Library from windows and then #NAME! for the cells. Any guesses??

[jjhayes]

Are your tabs named Sheet1 and Sheet2 in your file, as they are in the sample file?

[aware073]

No but I changed them in my formula.. they are "FE Inv with LPP" for Sheet1 and "Material & Vendors" for Sheet2..

=IF(COUNTIFS(FE Inv with LPP!$L$2:$L$1000,G$1,FE Inv with LPP!$A$2:$A$1000,$A4)>0,"X","")

[jjhayes]

Okay, if the sheets are called "FE Inv with LPP" and "Material & Vendors", then the formula in G3 on Materials & Vendors tab should be

Please Login or Register  to view this content.

If you see anything like "[Sample Spreadsheet.xlsx]", just erase what is in brackets.

If that works, then you should be able to copy formula from G3 to the other cells.

This assumes that materials are still in Column A on the FE Inv tab and the vendors are in Column L, while the vendor numbers on the Materials & Vendors tab still start at cell G1.

[aware073]

Got it!!! Thank you so much.. You are a lifesaver.