Pulling data across ranges and only within certain conditions.

I am attempting to build a string of formulas in order to collect and format combine two data fields. However this is only on the basis of conditions within the spreadsheet.

I have a multi sheet workbook with a lot of different and relevant information as it pertains to my work. Generally I want to combine two fields but only if they meet specific conditions in other columns within the spreadsheet.

Rank Full Name (last, first, MI) location driver/a-driver

I want to search the entirety of information and seek out the rank and name of specific individuals based on their location and whether they are a driver or a-driver. Likewise I want the cell that I want this data in it to begin with DRIVER: or A-DRIVER: based on the information contained in that column. I am not sure if I am doing this right so far but this is what I have come up with at this time. Any help with correcting the returning answers as "#value" would be greatly appreciated.

=IF(VLOOKUP("TAP ACT",Table2,18,TAP!R3:R105)&VLOOKUP("AD",Table2[DRIVER/A-DRIVER],19,TAP!S3:S105),CONCATENATE("DRIVER: ",Table2[[RANK]:[NAME]])," ") returns "#VALUE!

I have also attempted INDEX and MATCH formulas with no success. To date I have yet to return an answer except when I use on formula by itself.

Any help is greatly appreciated.

Joshua

I have also attempted

=if(Table2[DRIVER/A-DRIVER]="D","DRIVER: ","")& AND(Table2[LOCATION]="TAP ACT"),INDEX(CONCATENATE("DRIVER: ",Table2[[RANK]:[NAME]]),TAP!3:105,TAP!F:G))

I have now also attempted to use

=IF(AND(Table2[LOCATION]="TAP ACT",NOT(ISBLANK(Table2[DRIVER/A-DRIVER]="D"))),CONCATENATE(Table2[RANK]," ",Table2[NAME]),"")

which also returned the error #VALUE!

asdfasdfas

jkl;jkl;jk

jlchenoweth welcome to the forum

If your intention with posts 2 and 3 were to try and draw attention to your thread, forum rules require you to wait at least a day before "bumping".

Also, it always helps if you can upload a upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Looking at your formula...
=IF(VLOOKUP("TAP ACT",Table2,18,TAP!R3:R105)&VLOOKUP("AD",Table2[DRIVER/A-DRIVER],19,TAP!S3:S105),CONCATENATE("DRIVER: ",Table2[[RANK]:[NAME]])," ") returns "#VALUE!
that will always return an error because you are not following the IF() syntax...

=IF(test-this, do this if true, do that if false)
=IF(test-this
, do this if true
, do that if false)

You have...
=IF(VLOOKUP("TAP ACT",Table2,18,TAP!R3:R105)&VLOOKUP("AD",Table2[DRIVER/A-DRIVER],19,TAP!S3:S105)
,CONCATENATE("DRIVER: ",Table2[[RANK]:[NAME]])
," ")

You have no "test" in the 1st argument...
=IF(VLOOKUP("TAP ACT",Table2,18,TAP!R3:R105)&VLOOKUP("AD",Table2[DRIVER/A-DRIVER],19,TAP!S3:S105).................=???
you have 2 vlookups returning stuff but not testing that against anything

not it was not my intention to bump it up, I had input some updated information about what additional attempts I had made to address my issues and found out later that all I needed to do was to edit my original thread. So I went in and tried to delete the other posts (2&3) but was not able to and I couldn't save it unless I had 10 characters or more so I put in 10 characters and let it be. Sorry for the misunderstanding I just didn't see the reason to have multiple posts from myself when all I needed to do was edit my original.



Cleaned up spreadsheet for assistance.xlsx

In the attachment is the list of ranks, names, locations, and what vehicle they are driving, a-driving or riding as a passenger. I have made some modifications so that finding specific information may be easier instead of having it all in one cell. I look forward to seeing in anyone here can help me out with this.

Sheet3, cell B3 has the most recent formula attempt in order to get what I want in the cell.

What I am looking for is

if the person is the driver of a specific vehicle and it matches the sheet3 label of the vehicle and the individual has been labeled the driver in the TAP sheet, I want it to concatenate the rank and last name into the driver, a-driver, or passenger based on the information in the TAP sheet.