VLOOKUP w/ multiple Column_Index_Numbers

[CJhuhta]

I have two files.
File 1.) A sheet that I am trying to fill in. Using a PO umber from this sheet to search others sheets.
File 2.) A year long Calendar. Each Week has its own row. This file has a range from A7 - AB1767.

What I am trying to do is use a PO Number from File 1. Look for it in one of the columns in File 2.
Then on File 2, find the corresponding time that matches to the PO.
Then, auto populate that time into the empty cell on File 1.

I need to keep this as simple as possible but yet have it automated.
All i want to have to do is type the PO Number into File 1, then have the empty time cell populate from the information it finds in File 2.

I have tried many different vLookups, Index, ISNA, and Matchs.

This is the most recent thing I have tried, I feel like I am close on this however it is not working for me.
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]-->=IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,2,FALSE)))),IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,6,FALSE)))),IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,10,FALSE)))),IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,14,FALSE)))),IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,18,FALSE)))),IF(NOT(ISNA(VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,22,FALSE)))),IF(NOT(ISNA(N9VLOOKUP($J$2,'[New Appt Maker.xlsx]2018'!$1:$1048576,26,FALSE))))

If vLookup allowed me to have 7 column index numbers, that would make my life easier. The reason why i need 7, is per day on the calendar. PO NUmbers will be entered on any day of the week, and I need to look through the entire column to find the PO's.
The PO will be found in 1 of the 7 PO columns.
The Time (what I am wanting to autopopulate) will be found in the column next to it. The times can be found in columns 2, 6, 10, 14, 18, 22, 26.

What questions do you have for me??
Thanks!!

 

[JumpingCrab]

Does a particular PO number only show up once in the sheet in file 2?

 

[Aladin Akyurek]

Care to state clearly in which columns J2 can be found?
Care also to state clearly from which columns the results must be fetched?

For example, when J2 is in column 1 of

A7:AB1767, the result must be fetched from which column?​

 

[CJhuhta]

Yes, it will only be shown once.

 

[CJhuhta]

When J2 is in column A the result will be fetched from B. If its in column E, it will be found in column F. I will be in J. M in N. Q in R. U in V. Y in Z.
Hope this helps.

 

[Aladin Akyurek]

When J2 is in column A the result will be fetched from B. If its in column E, it will be found in column F. I will be in J. M in N. Q in R. U in V. Y in Z.
Hope this helps.

Let 2018!A7:AB1767 house the data (look up table).

In K2 of Sheet1 control+shift+enter, not just enter:

=VLOOKUP(J2,CHOOSE({1,2},INDEX('2018'!$A$7:$AA$1767,0,MIN(IF('2018'!$A$7:$AA$1767=J2,COLUMN('2018'!$A$7:$AA$1767)-COLUMN('2018'!$A$767)+1))),INDEX('2018'!$A$7:$AA$1767,0,1+MIN(IF('2018'!$A$7:$AA$1767=J2,COLUMN('2018'!$A$7:$AA$1767)-COLUMN('2018'!$A$767)+1)))),2,0)​

 

[CJhuhta]

I think this is working for me.
I will continue to test it.

Thank You!

 

[Aladin Akyurek]

I think this is working for me.
I will continue to test it.

Thank You!

You are welcome.