function that finds the right column to pull data from

[uspilotzzz]

I have two pages where I am copying and pasting data to pull financial info from. The sheets are named "ES option chain" and "SPX option chain" This works for me but if anyone else were to try and use it they might have different headers (Row 9) in a different order than me. I am pulling data from columns L (strike), I (ask), and G (bid). (see V11 on "ES options chain") Is there a way to have the functions that are accessing the values in these columns find the words "strike", "bid" and "ask" instead of the lettered column? This way, no matter what order someone has the columns ordered the functions would still be pulling the data they need from the correct columns.

Thanks

 

[coulombevin]

Hi uspilotzzz,

Here's my solution for excel 2016:

=XLOOKUP(V8,CHOOSE(MATCH("Strike",$C$9:$T$9,0),$C$10:$C$1000,$D$10:$D$1000,$E$10:$E$1000,$F$10:$F$1000,$G$10:$G$1000,$H$10:$H$1000,$I$10:$I$1000,$J$10:$J$1000,$K$10:$K$1000,$L$10:$L$1000,$M$10:$M$1000,$N$10:$N$1000,$O$10:$O$1000,$P$10:$P$1000,$Q$10:$Q$1000,$R$10:$R$1000,$S$10:$S$1000,$T$10:$T$1000),CHOOSE(MATCH("ASK",$C$9:$T$9,0),$C$10:$C$1000,$D$10:$D$1000,$E$10:$E$1000,$F$10:$F$1000,$G$10:$G$1000,$H$10:$H$1000,$I$10:$I$1000,$J$10:$J$1000,$K$10:$K$1000,$L$10:$L$1000,$M$10:$M$1000,$N$10:$N$1000,$O$10:$O$1000,$P$10:$P$1000,$Q$10:$Q$1000,$R$10:$R$1000,$S$10:$S$1000,$T$10:$T$1000),"Not found",0)

Bests regards,

Vincent

 

[Cubist]

Here's my solution for excel 2016:

XLOOKUP isn't available in 2016.

 

[coulombevin]

XLOOKUP isn't available in 2016.

Hi Cubist,

Are you sure? XLOOKUP function - Microsoft Support official page of Microsoft has this :

Applies To:
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones

I dont have access to that version so I dont know if there's a mistake.

Bests regards,

Vincent

 

[Peter_SSs]

@coulombevin
That Microsoft page is confusing. A little further down below what you quoted is this:

Note: XLOOKUP is not available in Excel 2016 and Excel 2019. However, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it, if it was created by someone else using a newer version of Excel.

 

[Peter_SSs]

@uspilotzzz

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: function to find the right colum to pull data from
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.


I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[coulombevin]

Hi uspilotzzz,

I tought about using INDEX, could you confirm if it work?

=INDEX($C$10:$T$1000,MATCH(V8,CHOOSE(MATCH("Strike",$C$9:$T$9,0),$C$10:$C$1000,$D$10:$D$1000,$E$10:$E$1000,$F$10:$F$1000,$G$10:$G$1000,$H$10:$H$1000,$I$10:$I$1000,$J$10:$J$1000,$K$10:$K$1000,$L$10:$L$1000,$M$10:$M$1000,$N$10:$N$1000,$O$10:$O$1000,$P$10:$P$1000,$Q$10:$Q$1000,$R$10:$R$1000,$S$10:$S$1000,$T$10:$T$1000),0),MATCH("ASK",$C$9:$T$9,0))

Bests regards,

Vincent

 

[Peter_SSs]

find the words "strike", "bid" and "ask" instead of the lettered column?

In your image there are two "BID" columns and two "ASK" columns. How do we know which ones to use?
Assuming it is the first (left-most) of each, this much shorter INDEX formula should do it for you.

=INDEX(C10:T209,MATCH(V8,INDEX(C10:T209,0,MATCH("Strike",C9:T9,0)),0),MATCH("ASK",C9:T9,0))