VLOOKUP Help

[LogPlanner]

This is my first post, so go easy on me.

I have a spreadsheet that I need a formula to check 3 different columns for a check mark and based off of which column contains the check mark, run VLOOKUP and return "Complete", "Incomplete" based off the spreadsheet it is looking at with VLOOKUP.

Right now I have the cell with this formula and it is returning the correct response.
=IF(VLOOKUP(D4,'fictional spreadsheet.xlsx]Sheet1'!$1:$1048576,16,False)>0,"Complete","Incomplete")
Attached is what I have.
So if CAT 3 has a check mark I want all the orange cells to be blank.
If CAT 2 has the check mark, the I want it to state "Complete" or "Incomplete". Complete meaning the cell on the VLOOKUP spreadsheet has something in it. Incomplete meaning the cell on the VLOOKUP spreadsheet is blank.
If CAT 1 has the check mark, the I want all the orange cells also to be blank.

I will use conditional formatting to black those out.

Thanks for any help you can give me.

 

[ides315]

I'm assuming that the top-left cell in your image is A1, just for reference's sake, which would put CAT 1, CAT 2, and CAT 3 in columns F, G, and H, and the first row of Completes/Incompletes in row 2. If there's actually more of the spreadsheet not pictured above/to the left though, you can just adjust the following accordingly.

You can use this formula in that first row, to match the relative location of the ✓ as 1, 2, or 3, and do something depending on where it matches:

=CHOOSE(XMATCH("✓",$F2:$H2), "", IF(VLOOKUP(…etc…)) ,"")

(Of course, replace IF(VLOOKUP(…etc…)) with your actual full formula.)

Last, IDK for sure exactly which "✓" character you're using in CAT 1 CAT 2 CAT 3. For the formula to work, it has to be the same check mark character, and there are several check marks out there. If yours is different from mine, then you need to copy the exact check mark from those cells, and paste it into the formula where the ✓ appears.

 

[LogPlanner]

I'm assuming that the top-left cell in your image is A1, just for reference's sake, which would put CAT 1, CAT 2, and CAT 3 in columns F, G, and H, and the first row of Completes/Incompletes in row 2. If there's actually more of the spreadsheet not pictured above/to the left though, you can just adjust the following accordingly.

You can use this formula in that first row, to match the relative location of the ✓ as 1, 2, or 3, and do something depending on where it matches:

=CHOOSE(XMATCH("✓",$F2:$H2), "", IF(VLOOKUP(…etc…)) ,"")

(Of course, replace IF(VLOOKUP(…etc…)) with your actual full formula.)

Last, IDK for sure exactly which "✓" character you're using in CAT 1 CAT 2 CAT 3. For the formula to work, it has to be the same check mark character, and there are several check marks out there. If yours is different from mine, then you need to copy the exact check mark from those cells, and paste it into the formula where the ✓ appears.

It works!
Thank you!
I changed the check mark to "X" just to make it easier.
here is what it ended up being
=CHOOSE(XMATCH("X",$F3:$H2),"",IF(IF(VLOOKUP(D4,'fictional spreadsheet.xlsx]Sheet1'!$1:$1048576,16,False)>0,"Complete","Incomplete"),""

Now for the next question.

I want to share this to the community I work in to help everyone out, but not everyone will understand excel and the formulas.
Is it possible to create a tab on the spreadsheet where someone can just past the name and location of the spreadsheet and the formula will update it?
Most people will be saving the information on a network drive, one drive or possible a TEAMS page.

 

[LogPlanner]

It works!
Thank you!
I changed the check mark to "X" just to make it easier.
here is what it ended up being
=CHOOSE(XMATCH("X",$F3:$H2),"",IF(IF(VLOOKUP(D4,'fictional spreadsheet.xlsx]Sheet1'!$1:$1048576,16,False)>0,"Complete","Incomplete"),""

Now for the next question.

I want to share this to the community I work in to help everyone out, but not everyone will understand excel and the formulas.
Is it possible to create a tab on the spreadsheet where someone can just past the name and location of the spreadsheet and the formula will update it?
Most people will be saving the information on a network drive, one drive or possible a TEAMS page.

Miss typed the formula in the response. Actual formula is:
=CHOOSE(XMATCH("X",$F3:$H2),"",IF(VLOOKUP(D4,'fictional spreadsheet.xlsx]Sheet1'!$1:$1048576,16,False)>0,"Complete","Incomplete"),"")